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?t=382670&highlight=started
hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...
