Import Sheet error

[Panik]

I am trying to import a sheet from one excel file into a new file.
I created the below to accomplish this saving it into "PERSONAL.XLSB"

When I execute the code, I keep getting:

Run-Time error '1004':
Copy method of Worksheet class failed

I am able to open the sourceworkbook as expected, but then I get an error on the "Copy After:" Line.

For the life of me, I can't figure out what is wrong. Any help is greatly appreciated.

Sub Copy_Frontlog()


Dim sourceworkbook As Workbook
Dim currentworkbook As Workbook


Set currentworkbook = thisworkbook


MyFile = Application.GetOpenFilename()
Set sourceworkbook = Workbooks.Open(MyFile)


sourceworkbook.Sheets("FrontLog").Copy After:=currentworkbook.Sheets("Sheet1")


sourceworkbook.Close


currentworkbook.Activate
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("A1").Select




End Sub

 

[Michael M]

Works fine for me...
Make sure the sheet names are correct AND neither workbook is protected !!

Sub Copy_Frontlog()
Dim sourceworkbook As Workbook, currentworkbook As Workbook
Set currentworkbook = ThisWorkbook
MyFile = Application.GetOpenFilename()
Set sourceworkbook = Workbooks.Open(MyFile)
sourceworkbook.Sheets("FrontLog").Copy After:=currentworkbook.Sheets("Sheet1")
sourceworkbook.Close
currentworkbook.Activate
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("A1").Select
End Sub

 

[Panik]

hmm. None of the sheets are protected. Also tried creating new files to test with. I get the exact same error. I even tried from scratch on another computer.

Any other thoughts? Thanks for the help.

 

[Michael M]

When checking the sheet names did you make sure there were no leading / trailling spaces in the sheet name ??

 

[Fluff]

Unless you are trying to copy the sheet into you personal.xlsb file, I suspect that this

Set currentworkbook = thisworkbook

should be

Set currentworkbook = ActiveWorkbook

 

[Panik]

Thanks Fluff!! You solved it!! I couldn't see that for the life of me! Thanks again.

Unless you are trying to copy the sheet into you personal.xlsb file, I suspect that this

Set currentworkbook = thisworkbook

should be

Set currentworkbook = ActiveWorkbook

 

[Fluff]

Glad we could help & thanks for the feedback