Results 1 to 5 of 5

Thread: VBA To Merge Multiple Workbooks In To Single File With One Sheet Per Workbook

  1. #1
    Junior Member
    Join Date
    Jun 2013
    Posts
    3
    Rep Power
    0

    Post VBA To Merge Multiple Workbooks In To Single File With One Sheet Per Workbook

    I am trying to take multiple workbooks (each containing one worksheet) and merge them into a single workbook with each file having its own worksheet within the file.

    I have the code below (I found several similar varieties on the web) but am receiving an error with the line
    'WS.copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt)'

    It seems that the code will open each workbook from the path but that the counter is not working so nothing is getting pasted.

    Any help would be much appreciated.

    Eva


    Code:
    Sub CombineFiles()
        Dim path            As String
        Dim FileName        As String
        Dim Wkb             As Workbook
        Dim WS              As Worksheet
        Dim ThisWB          As String
         
        ThisWB = ThisWorkbook.Name
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        path = "C:\MayT1\"
        FileName = Dir(path & "*.xls")
        Do Until FileName = ""
            If FileName <> ThisWB Then
                Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)
                For Each WS In Wkb.Worksheets
                    
                        WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
                    
                Next WS
                Wkb.Close False
            End If
            FileName = Dir()
        Loop
        Application.EnableEvents = True
        Application.ScreenUpdating = True
         
        Set Wkb = Nothing
        Set LastCell = Nothing
    End Sub
    Last edited by Excel Fox; 06-05-2013 at 11:11 PM. Reason: Code Tags

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Can't see any apparent errors. I tested the code, and it's working for me. Can you check if the master workbook doesn't have a workbook protection?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Jun 2013
    Posts
    3
    Rep Power
    0
    I 'unhid' my 'personal' wb associated w/ macros. Can't seem to find the 'tools' menu to unprotect the wb.

    The line of code that is giving me an error is:

    WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt)

    The 1st wb in the c: drive is added to the workbook and then it stops.

    Thanks for your suggestion.

    Eva

  4. #4
    Junior Member
    Join Date
    Jun 2013
    Posts
    3
    Rep Power
    0
    I don't seem to be able to 'enable macros' I've followed online instructions and saved the wb as xlsm but I still can't run any macros.

    the personal wb is showing as ~personal. Can this be an issue?

    Eva

  5. #5

Similar Threads

  1. Replies: 1
    Last Post: 06-07-2013, 10:32 AM
  2. Replies: 1
    Last Post: 05-09-2013, 08:56 AM
  3. Consolidate multiple workbooks from a folder into one master file VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 4
    Last Post: 02-26-2013, 09:00 PM
  4. Replies: 2
    Last Post: 12-19-2012, 08:28 AM

Tags for this Thread

Posting Permissions

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