Copying ranges from three worksheets in each workbook to master

[seagreen]

Hello,
I'm new to VBA and am reading "VBA and Macros for MS Office Excel 2007" and "Excel with VBA & .Net" but do not as yet know how to tackle the following:

I have numerous workbooks in a directory that I want to combine into a master workbook but why I am posting is because I haven't read a previous post that just copies ranges from three sheets in each workbook to the correspondingly named worksheets in the master. In other words, I want to preserve the master workbook and copy over the existing ranges with the data in the new incoming individual workbooks. I plan on using the same range for each (I have the toals in a row number that is static. I don't need to check for the end of the data range and adjust (I'll save that endeavor for a later date).

I see many programs that create a new master from individual workbooks in a directory but haven't found an example that loops through a directory and just copies each spreadsheet's contents to its corresponding sheet in the master.

THanks in advance for all responses. It is wonderful to be able to get answers to problems I have now while I am trying to learn!

 

[jeffreybrown]

Look at this page and see if it is a start.

http://www.rondebruin.nl/tips.htm

 

[seagreen]

Thanks jrb! That link is a great resource. I'm going to look at all the examples; I found one already that looks like it will even show me how to match up the worksheet names so that i can copy to each corresonding sheet in the master. If I get it all worked out, I'll come back and post the solution.
Thanks again.

 

[seagreen]

Is matching up worksheets (by name) in separate workbooks in order to copy a range from one to the other relatively easy and I'm just making it difficult?

Thanks in advance for any responses/clues.

 

[xenou]

Relatively easy in writing code... but relatively hard in the sense of requiring maintaining a list of worksheet names that must match in both books...

perhaps better:
Code a short procedure to read all the names from one of the workbooks first, then match them up (i.e., dynamic data).

If required, read names from both, make sure that each workbook has the same sheet names (compare the lists), then continue (i.e., validate the data).

Using an array to hold the names will make all of this easier.

Alex

 

[seagreen]

Thanks Alex, I'll look into matrices. I'm a beginner in VBA so I will keep reading and experimenting to see how to do what I need to do as described in the post above. Looks like Ron DeBruin's site has all kinds of code for copying and merging so I just need to continue experimenting. If I get it worked out, I will come back and post here. In fact, if I get the whole system going I'll post that too in case anyone ever needs something similar. Not that my code will be very elegant at this stage, except for the portions I use that the people here have helped me with, of course...