Results 1 to 5 of 5

Thread: Excel: Import sheets from various workbooks into another workbook using VBA

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    2

    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

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    directory = "c:\test\"
    fileName = Dir(directory & "*.xl??")

    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

    Workbooks(fileName).Close
    fileName = Dir()
    Loop

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    End Sub

    Does anyone have a suggestion to how to make this alteration? Furthermore I would like to change the sheetname as I import them to the new workbork.

    I would be very gratefull if anyone could help me out.

    Best Regards

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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
    Code:
            
            Workbooks(fileName).Worksheets("SheetToImport").Copy _
            after:=Workbooks("import-sheets.xls").Worksheets(total)
    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

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    2

    Re: Excel: Import sheets from various workbooks into another workbook using VBA

    Thank you so much! :-)
    Unfortunately an error message occurs:

    1004: Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook.

    I think this sound very weird!! Have you seen this before?
    Attached Images Attached Images  

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Excel: Import sheets from various workbooks into another workbook using VBA

    Is the sheet you're trying to insert from a newer version of Excel (.xlsx)?

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width