Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lightbulb Copy pasting a cell range involving three sheets and batch processing

    I am trying to convert a set of supplier data into our format final sheet using a Master sheet to do the job.
    It involves copy pasting the set of data a few times into these sheets. I have automated lot of steps in the mastersheet using formulae. But now I am looking for Batch processing these. Could someone please give an idea of what code will work.

    There are three sheets -
    1. "AAInput Data_Catalog 2279N.xls" - This is the Raw Data sheet.
    2. "AAMaster v2.0.xlsm" - This is the Master Sheet with Formulae to convert the raw data in the "AAInput Data_Catalog 2279N.xls" to the Final Sheet
    3. "AAFinal_2279N.xls"


    Steps:
    1. Open "AAInput Data_Catalog 2279N.xls" - Copy all the data on the "product Details" sheet. Close this Worksheet.
    2. Paste it in Cell "AT2" after the Blue colored column on the Products sheet of the Master worksheet "AAMaster v2.0.xlsm"
    3. Click "Rename Images" Button. (This renames Images using a Macro in some other Folder)
    4. Scroll to the Left on the Products sheet and copy the range of cells "C3:AR14". The range of cell has to be selected corresponding to the number of rows of the data we pasted. e.g. in this case upto row 14 - corresponding to location column value 2279.
    5. Copy these cells from Products sheet in the Master xls file and paste it into the final master sheet "AAFinal_2279N.xls" into "Products" sheet Cell "A2".
    6. Copy the corresponding data (Upto Product ID 30261) from the Masters "Attributes" sheet into the Final Attributes Sheet in "AAFinal_2279N.xls".
    7. Close the Input sheet "AAInput Data_Catalog 2279N.xls" and Final Sheet "AAFinal_2279N.xls". Keep the Master Sheet open. Repeat the process for Next Input sheet and create the Next Final Sheet as a Batch Process.

    Rgds
    SatyaAAInput Data_Catalog 2279N.xlsAAFinal_2279N.xlsAAMaster v2.0.xlsm

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Satya

    Welcome to the Lounge!

    I've had a look at your files, and yes, this process could be automated.

    I have some other questions, but, in the meantime, it would be helpful if you could post an additional sample file for the Next Input i.e. another sample Input Data Catalog file. Would all the files to be processed be located in the same subfolder????

    zeddy

  3. #3
    New Lounger
    Join Date
    Jun 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Additional Info

    Dear Zeddy

    Thanks for your response!

    Yes all these input files will be in the same folder and so will be the output files. Couple of other input files are attached for your reference plz.

    Also, I Only paste "Values" from Master sheet into the Final Sheet.

    It would also be great to have a second way to generate the output files i.e. All the output data copied into One Final Output Sheet only - one set after other in a single output file. The first way is to generate it separately for each Input Data File which I mentioned in the first post.

    Best rgds
    Satya

    Quote Originally Posted by zeddy View Post
    Hi Satya

    Welcome to the Lounge!

    I've had a look at your files, and yes, this process could be automated.

    I have some other questions, but, in the meantime, it would be helpful if you could post an additional sample file for the Next Input i.e. another sample Input Data Catalog file. Would all the files to be processed be located in the same subfolder????

    zeddy
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Satya

    ..I'm looking at this, please be patient.

    zeddy

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Satya

    I'm nearly ready to post my solution.
    I will create and use a template file to use for the 'AAFinal_xxx" files for each of the additional sample Input files you provided.
    probably ready tomorrow.

    zeddy

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Satya

    ..here is my version of a tool that will do as you asked.
    There are more changes that can be made, but this will give you a good start.
    If you have already done the job required, you could still use this as a basis for any future data conversions.

    This tool requires a blank template, which I have also attached.
    I used the sample Input files you posted in post#3 and post#1 for my testing.
    Any questions, please post back here.

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2015-06-27 at 07:42.

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Satya

    ..just to mention, check the vba code, and test before applying to your real data.
    Note: I didn't amend any of your formulas, but a lot of improvements can be made.
    Also, because you 'hard-coded' the image folders into your formulas, I commented the line that renames the image files, in my processing routine. You can uncomment that line for your own testing.

    zeddy

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •