CODEWITHSUNDEEP
excelruntime-errorworksheetvba
Andy Ardueser
Andy Ardueser

Reputation: 17

Run-time error '9' when copying worksheet to another workbook

I've been trying to write a macro to copy "Sheet1" from one workbook into another, but I keep getting a run-time error '9': Subscript out of range. 

Sub CopySheetToOtherWbk()

Dim CopyFromBook As Workbook
Dim CopyToWbk As Workbook
Dim ShToCopy As Worksheet

Set CopyFromBook = Workbooks("AllBugs.xlsx")
Set ShToCopy = CopyFromBook.Worksheets("Sheet1")
Set CopyToWbk = Workbooks("YourFriendlyNeighborhoodTemplateWorksheet.xlsx")

ShToCopy.Copy After:=CopyToWbk.Sheets(CopyToWbk.Sheets.Count)

End Sub

The highlighted line is "Set CopyFromBook = Workbooks("AllBugs.xlsx")". Not sure what I'm doing wrong here. Relatively new to VBA. Any help is appreciated.

Upvotes: 0

Views: 2067

Answers (1)

Andy G
Andy G

Reputation: 19367

The Workbooks collection refers to all currently open workbooks. If the workbook isn't open you will need to do this first.

Set CopyFromBook = Workbooks.Open("C:\Some Location\AllBugs.xlsx")

Upvotes: 1

Related Questions