Excel 2010: Subscript out of range error

Question

The error that I am receiving is Run-time error '9': Subscript out of range.

Sub Workbook_Open()

'Turn off any alerts that maybe displayed.
Application.DisplayAlerts = False
'Turn of the screen updates
Application.ScreenUpdating = False

'Declare the workbook, create it, save it and close it
Dim wk As Workbook
Set wk = Workbooks.Add
wk.SaveAs Filename:="C:\Saved File\KPI_Grid.xlsm", FileFormat:=52, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
wk.Close

'Open the workbook again. This will get rid of the 'Compatibilty View' and then activate the orginal workbook
Application.Workbooks.Open Filename:="C:\Saved File\KPI_Grid.xlsm"
Workbooks("KPI Grid V5K1 - macro testing.xlsm").Activate
Worksheets("Weekly").Activate
'Select all cells and copy them
Cells.Select
Selection.Copy
'Activate the workbook and sheet that we are going to paste into.
Workbooks("KPI_Grid.xlsm").Activate
Worksheets("Sheet1").Activate   ' ******************ERROR HERE ******************
Cells.Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
    , SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
'Activate the previous workbook again.
Workbooks("KPI Grid V5k1 - macro testing.xlsm").Activate
Worksheets("Monthly").Activate
Cells.Select
Cells.Copy
Workbooks("KPI_Grid.xlsm").Activate
Worksheets("Sheet2").Activate
Cells.Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
    , SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

End Sub

I thought this was a relatively simple task.
The aim is to create a new workbook, copy the values of two sheets over and then save and close the new workbook.
Why does this code error?

I just stumbled across an old post that you had commented about a similar situation. http://stackoverflow.com/questions/22815124/run-time-error9-subscript-out-of-range?rq=1 - how do you check titles for a sheet name? — DJenkins, Jun 24 '14 at 12:18

whytheq · Accepted Answer · 2014-06-24T16:00:30.423

2

Go to this line Worksheets("Sheet1").Activate
Press F9
Press F5
Check the screen - has the workbook "KPI_Grid.xlsm" been activated ?
Does it actually have a sheet called "Sheet1" ?

If Sheet1 is not in the same workbook as the code then this will naturally error - you need to ensure everything is qualified like this:

ActiveWorkbook.Worksheet("Sheet1").Activate

edited Jun 24 '14 at 16:00

answered Jun 24 '14 at 13:01

whytheq

34,466
65
172
267

Hi whytheq, I clicked did the above and it throws a run time error 1004. I ran a quick search around the net and it says that this maybe because my code is in the ThisWorkbook. Could this be the route of my issues? I also tried doing the above against the line above it and it also threw the same error. Regards, – DJenkins Jun 24 '14 at 14:37
@DJenkins - rather than F5 try just stepping through with `F8` - on what line does it first bug? – whytheq Jun 24 '14 at 15:52
....wait there - if Sheet1 is not in the same workbook as the code then of course it will error! – whytheq Jun 24 '14 at 15:53
1

ActiveWorkbook.Worksheet("Sheet1").Activate That worked beautifully. I thought that the line before Workbooks("KPI_Grid.xlsm").Activate did this. HMMMM, think its time to buy a VBA book and have a good read rather than piecing things together from around the net! and a big thank you for helping out. – DJenkins Jun 25 '14 at 09:32

Excel 2010: Subscript out of range error

1 Answers1