Copying a worksheet from a closed workbook to the active one

samc2696

New Member
Joined
Jul 16, 2018
Messages
38
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:
VBA Code:
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,607
Office Version
365
Platform
Windows
How about
VBA Code:
    sourceBook.Sheets("Summary").Copy Before:=ActiveWorkbook.Sheets(1)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,607
Office Version
365
Platform
Windows
How about
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,005
Messages
5,465,959
Members
406,456
Latest member
jmishra91

This Week's Hot Topics

Top