excel VBA error when copying a worksheet from workbook opened using activeworkbook.path

Question

I need to copy a summary worksheet from 12 different workbooks into a regional workbook. I get a debugging error on the line that should copy the worksheet into the regional workbook. I tried using both the name and worksheet number on both the source and current workbooks. The code is included below.

Sub copyfc()

Dim sourceworkbook As Workbook
Dim currentworkbook As Workbook

Set currentworkbook = ThisWorkbook

Set sourceworkbook = Workbooks.Open("C:\Country Files\BE FC12 BU19.xlsx")

sourceworkbook.Sheets("BE").Copy after:=currentworkbook.Sheets("End")

sourceworkbook.Close


End Sub

Shai Rado Shai Rado · Accepted Answer · 2019-01-15T01:17:00

The line below will place the worksheet at the end:

sourceworkbook.Sheets("BE").Copy After:=currentworkbook.Sheets(currentworkbook.Sheets.Count)

excel VBA error when copying a worksheet from workbook opened using activeworkbook.path

1 Answers