Multiple Pastes Between workbooks

[WBTKbeezy]

I have 3 workbooks, each contains the same 30 tabs, but each workbook
contains different information. There is a main workbook, and two extra with
information that I need to put all together on the main workbook. I am
looking for the easiest way to paste the info from the 2 workbooks into the
main work book without having to do it tab by tab and workbook by workbook.
Anyone have any suggestions?

[broro183]

Hi,
I'm hoping that you know how to use macros, if not, you may find the link below useful:
( http://www.mvps.org/dmcritchie/excel/getstarted.htm )

1 Open all three files.
2 Copy the macro below into a module of the VBA project of your main workbook (in Excel press [ALT + F11] (this opens the VB Editor), right click on the VBA project of your file, Insert-Module).
3 Paste the code below into the resulting sheet that appears.
4 Change the file names in the appostrophes (as commented), change the ranges as required & run the macro by placing the cursor within the code .
Please note:
* may have word wrapping (the space & underscore, ie " _") in the wrong places,
* that the below does not include error handling,
* hasn't been tested for speed or handling of large data sets,
* will over write any data that exists in the destination cells of the main workbook,
* and most of all, requires all the sheet names to be exactly the same in each of the 3 workbooks.

Sub CopyThirtyTabs()
Dim CopiedFrom1 As String
Dim CopiedFrom2 As String
Dim MainWorkbook As Workbook
Dim ws As Worksheet
Dim i As Long
Dim DataSheetName() As String

CopiedFrom1 = "copiedfrom1.xls" 'change file name as needed
CopiedFrom2 = "copiedfrom2.xls" 'change file name as needed
Set MainWorkbook = ThisWorkbook

' Store the sheet names as separate strings
ReDim DataSheetName(1 To 30) 'The # of tabs
For Each ws In MainWorkbook.Worksheets
i = i + 1
DataSheetName(i) = ws.Name
Next ws

'Copies selected data from each of the workbooks into the main one for _
each of the 30 sheets.
For i = 1 To 30
Workbooks(CopiedFrom1).Worksheets(DataSheetName(i)).Range("a1:b10").Copy _
MainWorkbook.Worksheets(DataSheetName(i)).Range("a1:b10")

Workbooks(CopiedFrom2).Worksheets(DataSheetName(i)).Range("a1:b10").Copy _
MainWorkbook.Worksheets(DataSheetName(i)).Range("c1:d10")
'Note the different destination range for pasting into on the second copy.
Next i
MsgBox "all done :-)"
End Sub


btw, Another similar thread may also help you:
http://excelforum.com/showthread.php...hlight=started

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...