将工作表从一个工作簿复制到另一个工作簿

Question

试图从当前工作簿“Create Report.xlsm”中复制工作表名称“Headings Explantions”，该工作簿打开到我要求打开的工作簿中，我得到了下标超出范围的错误

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

Answer 1

myfile是完整的驱动器、文件夹路径和文件名。若要引用工作簿集合中打开的工作簿，只需要文件名。将工作簿var设置为打开的工作簿，然后使用它。

床单。伯爵是单数；应该是工作床单。计数或床单。计数。

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