Multiple Pastes Between workbooks
 

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?

Multiple Pastes Between workbooks
 

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("a 1:b10")


Workbooks(CopiedFrom2).Worksheets(DataSheetName(i) ).Range("a1:b10").Copy
_
MainWorkbook.Worksheets(DataSheetName(i)).Range("c 1: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...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=526139