none
How Do I Create a Loop to Copy Data From One Workbook to Another? RRS feed

  • Question

  • Hi all

    I'd appreciate some guidance setting up a loop.

    I want to copy blocks of data from one workbook to another. I have two workbooks; one contains 50 worksheets of .csv data in range A1:B180 on each worksheet. The other workbook contains 50 worksheets that are identically named and in the same sequence as those in the .csv workbook, plus a summary worksheet (sheet 1) that draws its data from the other 50 worksheets via vlookup formulae. I want to copy the data from the .csv worksheets into the corresponding worksheet in the other workbook. In this instance, vlookup isn't really practicable as the worksheet names frequently change.

    How do I set up a loop to achieve this? I've tried but I just can't get it to loop properly. The trick seems to be either in getting the coding to recognise that the worksheet names match, or to get it to move each workbook forward one sheet after doing the data transfer.

    Whatever, it's a task that I have to perform every week or so and which I'm currently doing by manually copying each data block. A properly constructed loop will be a great time saver.

    Guidance from the VBA gurus out there will be most welcome.
    Friday, February 5, 2016 2:42 AM

Answers

  • Hi DaviDWF2,

    Thank you for your sending an email to me.
    I'd sent you an email with two files attached.
    If Excel macro/VBA works fine, please mark this reply as answers.

    Regards.

    • Marked as answer by DaviDWF2 Thursday, February 11, 2016 12:05 AM
    Sunday, February 7, 2016 2:07 AM

All replies

  • Hi DaviDWF2,

    Can you share two sample files(with VBA code, if possible) via cloud storage such as OneDrive, Dropbox, etc.
    If you can,
      1) please modify values/data in them appropriately.
      2) please decrease sheets from 50 to 5 or so.

    Ragards.
    Friday, February 5, 2016 4:19 AM
  • OK - I'll amend/shrink the files and get back to you
    Friday, February 5, 2016 5:29 AM
  • OK - I've now re-created the workbooks; I notice the data doesn't quite match between the two workbooks but it's only because I had to reconstitute the .csv files. When the main workbook was updated the data would have matched. I can't give you current data because I've got a boot problem with my desktop. In normal (manual) operation I run a macro that clears columns B & C before I commence the update process, so the .csv data is entered into columns full of blank cells.

    Now - one thing that I wasn't aware of. I didn't realise that the workbook containing the .csv data is in fact created from individual Excel files. These files are then put into a workbook with 1 tab for each file. The tabs are then named the same as the original data file, so what starts out as AA1.csv becomes tab AA1 in the newly created workbook. This seems to be done as a housekeeping exercise, rather than for any programming value, and is a practice that I've now stopped.

    So - the amended requirement is for the coding to start at tab AA1 in workbook A200B and copy range A1:B180 from AA1.csv to B1:C180 in tab AA1; move to tab AA2, copy range A1:B180 from AA2.csv to B1:C180 in tab AA2; move to tab AB1, copy range A1:B180 from AB1.csv to B1:C180 in tab AB1; move to tab AB2 . . . . and so on through the entire workbook.

    Hopefully that change is a simplification and not a complication.

    Just a thought - I'd prefer, if practicable, to avoid naming the tab that the coding moves to, because the .csv file names change over time. I'd prefer to keep it a generic loop - sort of "from i to . . . next i" style of thing, then have it read the name of the tab and look for a .csv file with that prefix. Hope that's clear. I manually keep the tab names current so there's no risk of searching for a non-existent .csv file.

    The link to access the uploaded files is:
    https://www.mediafire.com/folder/j5aurr7f6ny2u/Excel_-_VBA_-_Data
    I've uploaded:
      the 5 original .csv files;
      the workbook (23Nov15_Data.xlsx) that was created using these files; and
      the workbook (A200B.xlsm) that the .csv files should be copied into.

    I trust that's all OK. Let me know if you need further clarification.

    Many thanks
    Friday, February 5, 2016 7:59 AM
  • Hi DaviDWF2,

    Thanks. I could downloaded 7 files.  They are unopened now.
    Please wait with much patience, for it's evening in japan and it takes time for me to understand your description. 

    Regards.
    Friday, February 5, 2016 8:10 AM
  • Hi,

    I ask you to do...
    1) I opened "A200B.xlsm", and found link errors.
        Inform me the right source of them.
        
    2) Can you make a diagram(figure?) that explains the relation of each file/sheet?
        for example... "Relation_ash.zip"
        (one diagram would be more eloquent than many words)

    Regards.


    • Edited by Ashidacchi Friday, February 5, 2016 8:52 AM
    Friday, February 5, 2016 8:48 AM
  • Hi,

    What you described is somewhat difficult for me to understand.
    So, I've made a diagram that shows the relation of data(each file) and their format.
    How do you think?
    "Relation_ash_2.zip"

    Regards.
    Friday, February 5, 2016 10:24 AM
  • Hi Ashidacchi

    Firstly, thank you so much for your interest in assisting me.

    Yes, your understanding of my current process is completely correct. I would suggest though that from here on we skip the step in your column D. As I said in my post, I didn't realize that the individual .csv files were being converted into a workbook. That conversion is done manually, takes a lot of time and to me seems to be a waste of time. If it's possible to code it, I would think it's simpler to just work with workbook A200B.xlsm and the .csv files and completely disregard workbook 23Nov15_Data.xls. In other words - delete column D and go straight from column B to column F.

    Referring to your comment about the link errors, I don't see that being a problem. There's no dependence between any of the files so the link error won't cause a problem. Data is copied from one file to the other, not linked. I had to re-create the data file for you from old data so the file names are out of date anyway.

    The important thing is to copy the data from from AA1.csv into worksheet/tab AA1, then from AA2.csv into tab AA2, then from AB1.csv into tab AB1, . . . and so on.

    Trust that's clear.

    Thanks again.
    Friday, February 5, 2016 11:47 AM
  • Hi DaviDWF2,

    Please remember that I'm a self-employed individual, developing Windows software, don't work for Microsoft or this forum. As a volunteer, I've joined this or that forums. 

    Please modify a diagram/figure I've shared, and show it (it's an Excel file easy to modify). I'm poor at English and don't want to read English much.
    I suppose it will be a few days after that many words are necessary between us. Now is the time when I must understand general data-flow, not detail such as Column, Cell... 

    Regarding "link error", you would reproduce it, if you download shared files and save them a new folder. That is in my case.

    Regards.
    Friday, February 5, 2016 12:59 PM
  • Hi Ashidacchi

    I've amended your diagram and uploaded it. Use the same URL as before:

    https://www.mediafire.com/folder/j5aurr7f6ny2u/Excel_-_VBA_-_Data

    Please ignore the link error. The files are not linked.

    Many thanks

    Friday, February 5, 2016 11:19 PM
  • Good morning (in Japan)

    Thank you, DaviDWF2.
    I could download and open your file.
    I'll try to open *.csv files and paste its contents to A200B.xlsm.
    (I'm not sure whether it's possible, for I've never done it) 

    Regards. 
    Friday, February 5, 2016 11:45 PM
  • Hi DaviDWF2,

    My work is almost finished. I want to provide it via email (as an attached file). 
    Please email me in order for me to know your email address.
    My email address:
      ash.m314 + at mark + hokusosha + dot + com
    Regards.

    P.S.
      DataFlow (PDF file)
    • Edited by Ashidacchi Sunday, February 7, 2016 12:22 AM
    Sunday, February 7, 2016 12:07 AM
  • Hi DaviDWF2,

    Thank you for your sending an email to me.
    I'd sent you an email with two files attached.
    If Excel macro/VBA works fine, please mark this reply as answers.

    Regards.

    • Marked as answer by DaviDWF2 Thursday, February 11, 2016 12:05 AM
    Sunday, February 7, 2016 2:07 AM