Copying ranges from three worksheets in each workbook to master

seagreen

Board Regular
Joined
Feb 7, 2009
Messages
71
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!
 

seagreen

Board Regular
Joined
Feb 7, 2009
Messages
71
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

Board Regular
Joined
Feb 7, 2009
Messages
71
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,550
Office Version
2013
Platform
Windows
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

Board Regular
Joined
Feb 7, 2009
Messages
71
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...
 

Forum statistics

Threads
1,081,470
Messages
5,358,874
Members
400,514
Latest member
JoHio2577

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top