Combining from multiple workbooks

Budinski

New Member
Joined
Mar 2, 2009
Messages
17
I will try to be as accurate as possible cos its more complicated than i first thought.

I have multiple WB's all with the same format in a single folder. I need a button to copy all the text from each WB into a single Master WB that has the same format. There are 3 sheets in each client WB corresponding to three sheets in the Master WB. Each client WB has a number of rows (or none) on each sheet and when they are copied to the Master they need to paste consecutively and into the corresponding Master sheet.

Something like this:

Copy rows from [WB1].Sheets 1, 2 and 3 (starting at a:4, columns A-Q). Then paste to corresponding [MasterWB].Sheets 1, 2 and 3 (at a:4, columns A-Q) then repeat with Next Book.

I hope thats clear enough. The Client WB's are all named "stats [name].xls" with 1 hidden sheet (to populate lists) and 3 sheets named "POC", "ISS" and "ECS" repectively. The Master WB is named Stats.xls with the same sheet names as the Client WB's.

The following code was written for me by a helpful member of this forum but it only copies the first sheet of each Client WB. When i tried to duplicate and modify it to copy the second and third sheets I could not get it to copy from the second/third sheets and it meant 3 buttons/3 steps/3 times the confusion. :confused:

Code:
Sub Report()
a = 1:
st:
If Sheets(5).Cells(a, 1) = "" Then GoTo endd
Path = Sheets(5).Cells(a, 1).Text
If Dir(Path) = "" Then
w = MsgBox(Path + " Is Not A Valid Path / File", , "REPORT")
a = a + 1: GoTo st
End If
Application.ScreenUpdating = False
Workbooks.Open Path
Ro = 4:
st1:
If Cells(Ro, 1) = "" Then GoTo NextBook
 CopyIt Ro
Ro = Ro + 1: GoTo st1
NextBook:
ActiveWorkbook.Close savechanges:=False
a = a + 1: GoTo st
endd:
Application.ScreenUpdating = True
Sheets(1).Activate
End Sub
Sub CopyIt(Ro)
With ThisWorkbook.Sheets(2)
roo = 4
st:
If .Cells(roo, 1) <> "" Then roo = roo + 1: GoTo st
For x = 1 To 17
.Cells(roo, x) = Cells(Ro, x).Text
Next x
End With
End Sub

If there is anyone who could help me with this I would very much appreciate it. I am only a basic user of Excel and VBA is still new to me. Adding modules and understanding basic commands is as much as I know at the moment. Thanks in advance to anyone who has time to work on this. :)
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Seems like that would work for one workbook, but as soon as you ran it on a second workbook it would paste over the data from workbook 1.

does workbook 2 paste into a different section of the master?

Unless you're looking to create a wkb1sht1, wkb1sht2, wkb1sht3,wkb2sht1, wkb2sht2, etc
 
Last edited:
Upvote 0
That code actually worked perfectly for the first sheet. It pasted each row on a separate line exactly as required. The problem was that it was only the first sheet for each Client WB.
 
Upvote 0
yes, each workbook on a different sheet right?

If you want multiple sheets, do you want them pasted on the same sheet in the master, or seperate sheets in the master? Workbooks will be on seperate sheets, but will worksheets from the client WBs also be on seperate sheets?

This matters because if you want them on seperate sheets, then the macro needs to create sheets in the master workbook from the source workbook AND worksheet names
 
Upvote 0
Copy rows from [WB1].Sheets 1, 2 and 3 (starting at a:4, columns A-Q). Then paste to corresponding [MasterWB].Sheets 1, 2 and 3 (at a:4, columns A-Q) then repeat with Next Book.

If Client workbook 2 pastes its worksheets 1, 2, and 3 to 1, 2, and 3 in the master... then you just overwrote client workbook 1
 
Upvote 0
In this case I have about 13 client files and I need the 13 clients Sheet1 to combine into the Master Sheet1, then the 13 Sheet2 to combine into the Master Sheet2 etc. Does that make sense? :p
 
Upvote 0
Yeah, thats what I want to avoid :)

It needs each client row to paste on the next row in the master to avoid overwriting.
 
Upvote 0
Silly Question... Does your client workbooks increase/decrease regularly?

In other words... if you use the same client workbooks all the time, why not just use references intead of VBA?

IE: in cell A1 of Sheet 1 of your Master.xls use:

Code:
='[Client 1.xls]Sheet 1'!$A$1
then drag it down and across.
That way it auto-populates with all your client workbook data when you open it.
 
Last edited:
Upvote 0
The Client workbooks are updated each month and emailed where they need to be combined. Im not really sure of the best way to do it. I wanted to make it simple for whoever might do the combining, hence the button idea.

The amount of rows in the client WB's can increase and decrease.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top