Excel: Import sheets from various workbooks into another workbook using VBA
Hi all,
I'm basically trying to copy around 20 sheets, all with the same sheetname, from different workbooks, into another workbook using VBA. I have managed to import all sheets from a number of workbooks, but I only need one specific worksheet from each workbook. This code looks like:
Private Sub CommandButton1_Click()
Dim directory As String, fileName As String, sheet As Worksheet, total As Integer
Do While fileName <> ""
Workbooks.Open (directory & fileName)
For Each sheet In Workbooks(fileName).Worksheets
total = Workbooks("import-sheets.xls").Worksheets.count
Workbooks(fileName).Worksheets(sheet.Name).Copy _
after:=Workbooks("import-sheets.xls").Worksheets(total)
Next sheet
Re: Excel: Import sheets from various workbooks into another workbook using VBA
If you name this specific sheet in each workbook you'd like to import from the same (e.G. "SheetToImport") you can use this String/Name to directly pick the sheet from the Sheets-Collection
Get rid of the For-Each-Loop, since you're directly picking the sheet
How to rename a sheet with VBA is easy. Just look up the method in your help-file
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
Re: Excel: Import sheets from various workbooks into another workbook using VBA
Excel2003 can only handle some 65k of rows per sheet, so like vbfbryce said: does the imported sheet has more than 65k of rows
EDIT: i forgot:
you have to rename each imported sheet after importing it and before importing the next or you'll get a name-collision
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad