locked
Consolidate multiple workbooks into one workbook RRS feed

  • Question

  • Hello Developers!

    I am trying to consolidate multiple workbooks in one single workbook but I am getting an error on this line of my code: 

    When I debug it, the highlighted issue is: Sheet.Copy After:=ThisWorkbook.Sheets(1)

    And the error message is: Method "copy"of object_Worksheet failed

    Please advise why it is erroring out on this line and below are my code: 

    Your help is greatly appreciated! 

    Option Explicit
    Const path As String = "C:\Users\sea-lucilleb\Desktop\New folder\"
    Sub GetSheets()
    Dim Filename As String
    Dim wb As Workbook
    Dim Sheet As Worksheet
    
    
    Filename = Dir(path & "*.xls*")
    Do While Filename <> ""
        Set wb = Workbooks.Open(Filename:=path & Filename, ReadOnly:=True)
        For Each Sheet In wb.Sheets
            Sheet.Copy After:=ThisWorkbook.Sheets(1)
            'sheet.Copy After:=ThisWorkbook.Sheets
        Next Sheet
        wb.Close
        Filename = Dir()
    Loop
    End Sub


    • Edited by IamJackie Wednesday, November 14, 2018 4:39 PM
    Wednesday, November 14, 2018 4:05 PM

All replies

  • Iam, I have no errors when run your code. It work fine.

    Excell for Office 365 MSO (16.0.11001.20064) 32 bits

    Windows 10 64 bits

    Wednesday, November 14, 2018 5:33 PM
  • Hi Jackie,

    I run your code in Excel 2016(64 bits).

    When i run your code in "ThisWorkbook", i get an error "Member already exsists in an object module from which this object module derives".

    So i insert a module and run your code in it. It works well for me.

    You can test it in the module to see if it works for you.

    Hopefully it helps you.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Thursday, November 15, 2018 2:29 AM
  • Hello Yuki! Thank you for your response! The code partially worked upon your recommendation. However, my hope is that it will paste all the sheets into just one sheet/tab. Right now it is copying the data from the folder but it is pasting it in separates tabs in one workbook. How to resolve this?
    Thursday, November 15, 2018 5:38 PM