Copying a worksheet from a closed workbook to the active one

[samc2696]

Hi all,

I want to copy a sheet from a workbook I have saved to the one I am currently working on. I need this code to be able to do this in any workbook I am working on.

The code so far is as such:

Dim sourceBook As Workbook
    Application.ScreenUpdating = False
    Set sourceBook = Workbooks.Open("filepath")
    sourceBook.Sheets("Summary").Copy Before:=ThisWorkbook.Sheets(1)
    sourceBook.Close
    Application.ScreenUpdating = True

I have my file's path where it says so above, and I want to copy the worksheet called 'Summary' and paste it before the sheet in the active workbook.

I keep getting a run-time error 1004 - Copy method of Worksheet class failed

Thank you for the help

 

[Fluff]

How about

    sourceBook.Sheets("Summary").Copy Before:=ActiveWorkbook.Sheets(1)

 

[samc2696]

How about

    sourceBook.Sheets("Summary").Copy Before:=ActiveWorkbook.Sheets(1)

Hi - that didn't work - instead that made it duplicate in the sourceBook

 

[Fluff]

How about

Dim sourceBook As Workbook, MyBook As Workbook
   Set MyBook = ActiveWorkbook
   Application.ScreenUpdating = False
   Set sourceBook = Workbooks.Open("filepath")
   sourceBook.Sheets("Summary").Copy Before:=MyBook.Sheets(1)
   sourceBook.Close
   Application.ScreenUpdating = True