Copy worksheet from one Workbook to another Workbook

Question

Trying to copy a worksheet name "Headings Explanations" from the current workbook "Create Report.xlsm" that is open to a work book that i have asked to be opened I get subscript out of range error

Private Sub CommandButton2_Click()

Dim myfile As String

myfile = Application.GetOpenFilename(Title:="Please choose a file to open", FileFilter:="Excel Files *.xls* (*.xls*),")

Workbooks.Open Filename:=myfile

Workbooks("Create Report.xlsm").Sheets("Headings Explanations").Copy After:=Workbooks(myfile).Sheets(Sheet.Count)

End Sub

Unknown Unknown · Accepted Answer · 2018-09-18T11:04:33

myfile is the full drive, folder path and filename. To reference an open workbook in the Workbooks collection, you only want the filename. Set a workbook var to the opened workbook and use that instead.

Sheet.count was singlular; should be workSheets.count or sheets.count.

Private Sub CommandButton2_Click()

    Dim myfile As String, wbo as workbook

    myfile = Application.GetOpenFilename(Title:="Please choose a file to open", FileFilter:="Excel Files *.xls* (*.xls*),")

    set wbo = Workbooks.Open(Filename:=myfile)

    Workbooks("Create Report.xlsm").workSheets("Headings Explanations").Copy After:=wbo.workSheets(workSheets.Count)

End Sub

Copy worksheet from one Workbook to another Workbook

1 Answers