Macro to copy tab from closed workbook not working

[_eNVy_]

Hi all,

The below code doesn't seem to be working:

Sub CopySheet()

Application.ScreenUpdating = False
 
    Set closedBook = Workbooks.Open("C:\Users\040429\Documents\Workings\Excel\_Holidays_.xlsb")
    closedBook.Sheets("Holidays ").Copy Before:=ThisWorkbook.ActiveSheet
    closedBook.Close SaveChanges:=False
 
Application.ScreenUpdating = True
End Sub

I want to open up the Holidays file and copy "Holidays " tab and paste into the Active Workbook from which I initiated the macro.
However I get an message : Run-time error '1004'
Copy method of Worksheet class failed.

Help on this would be really helpful!
Thanks!

 

[mumps]

Try:

Sub CopySheet()
    Application.ScreenUpdating = False
    Dim desWB As Workbook
    Set desWB = ThisWorkbook
    Set closedBook = Workbooks.Open("C:\Users\040429\Documents\Workings\Excel\_Holidays_.xlsb")
    closedBook.Sheets("Holidays ").Copy Before:=desWB.ActiveSheet
    closedBook.Close SaveChanges:=False
    Application.ScreenUpdating = True
End Sub

 

[_eNVy_]

Try:

Sub CopySheet()
    Application.ScreenUpdating = False
    Dim desWB As Workbook
    Set desWB = ThisWorkbook
    Set closedBook = Workbooks.Open("C:\Users\040429\Documents\Workings\Excel\_Holidays_.xlsb")
    closedBook.Sheets("Holidays ").Copy Before:=desWB.ActiveSheet
    closedBook.Close SaveChanges:=False
    Application.ScreenUpdating = True
End Sub

Thank you for your swift response. I am still getting an error sadly.

The code highlighted is

closedBook.Sheets("Holidays ").Copy Before:=desWB.ActiveSheet

 

[mumps]

Do you have a sheet named "Holidays" in the closed workbook?

 

[_eNVy_]

Yes. Well it is "Holidays " with a space.

 

[mumps]

As far a I can see the code should work. Could you upload a copy of your Holidays_.xlsb file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[_eNVy_]

Hi mumps - the link to the file is here:

Gofile - Free Unlimited File Sharing and Storage

Gofile is a free, secure file sharing and storage platform. With unlimited bandwidth and storage, you can easily store and share files of any type without any limits. Our advanced features, such as CDN support and password protection, make Gofile the ideal choice for individuals and businesses...

gofile.io

Thank you for your help!

 

[mumps]

I tested the macro and it worked properly. After you run the macro, check to see if the "_Holidays_.xlsb" file has been opened by the macro before it generates the error.

 

[_eNVy_]

I tested the macro and it worked properly. After you run the macro, check to see if the "_Holidays_.xlsb" file has been opened by the macro before it generates the error.

Hi mumps - apologies for the late reply.
Running the code line by line, it does open the "_Holidays_.xlsb" file and still errors on :

    closedBook.Sheets("Holidays ").Copy Before:=desWB.ActiveSheet

 

[_eNVy_]

This has been fixed!!!!

I changed the properties of desWB from ThisWorkbook to ActiveWorkbook.
This has solved all my problems.

Thank you mumps for your help, would not have gotten to where I am without it.

Take care!