runtime error 9 subscript out of range help

[skate1991]

Hi,

I have created the below code, which when I last used it was working fine. but it now has a runtime error 9 and I'm not sure why this is. I have highlighted the part of the code with this runtime error.

I'm not sure if its down to this but the file we are opening gets saved over everynight with the updated figures.

I dont know if this will make a difference but in the code below I have changed the file path location to just "file path" and the excel document its opening from that path to "KPI.xlsx" these arent the correct names but due to certain reason I cant upload the actual file path and name.

Sub theone()
'
' theone Macro
'

'
Sheets("Instructions download").Select
Range("A8").Select
Range("A8:T60000").Select
Selection.ClearContents
Range("A8").Select
Sheets("Aborted download").Select
Range("A8:O60000").Select
Selection.ClearContents
Range("A8").Select
Sheets("Completions download").Select
Range("A8").Select
Range("A8:V60000").Select
Selection.ClearContents
Range("A8").Select
Windows("Volumes.xlsx").Activate
Workbooks.Open Filename:= _
"Fil"
Sheets("Completions").Select
Range("E12").Select
Selection.AutoFilter
Range("D12").Select
ActiveSheet.Range("A6:V60000").AutoFilter Field:=6, Criteria1:= _
xlFilterThisMonth, Operator:=xlFilterDynamic
Range("A8").Select
Range("A8:V60000").Select
Selection.Copy
Windows("Volumes.xlsx").Activate
Range("A8").Select
ActiveSheet.Paste
Sheets("Aborted download").Select
Windows("KPIs.xls").Activate
Sheets("Aborted").Select
Range("D14").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveSheet.Range("A6:O60000").AutoFilter Field:=7, Criteria1:= _
xlFilterThisMonth, Operator:=xlFilterDynamic
Range("A18").Select
Range("A8:O60000").Select
Selection.Copy
Windows("Volumes.xlsx").Activate
ActiveSheet.Paste
Range("A8").Select
Sheets("Instructions download").Select
Windows("KPIs.xls").Activate
Sheets("Completions").Select
Windows("KPIs.xls").Activate
Sheets("Instructions").Select
Range("D17").Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("C17").Select
ActiveSheet.Range("A6:T60000").AutoFilter Field:=4, Criteria1:= _
xlFilterThisMonth, Operator:=xlFilterDynamic
Range("A265").Select
Range("A8:T60000").Select
Selection.Copy
Windows("Volumes.xlsx").Activate
ActiveSheet.Paste
Range("A8").Select
Application.CutCopyMode = False
Calculate
ChDir _
"File Path "
ChDir _
"File Path"
ActiveWorkbook.SaveAs Filename:="File Path" & Format(Now(), "DD-MM-YYYY")
End Sub

[AB33]

Usually, run out of range could mean: The sheet you are using does not exist on the sheet collections. On practical level, since your code was working, it could mean you have changed the sheet name, so the code could not find it any more.
The same pattern could apply to a range.

[skate1991]

Hi AB33,

Thanks for looking into this.

It opens up the KPI workbook fine now, the error comes when it goes to sheets("Completions").Select

I have re recorded opening up the KPI workbook and clicking on the completions sheet, and then copying that code into the exsisting and that still hasnt fixed it. Anymore ideas.

Workbooks.Open Filename:= _
"File Path KPIs.xls"
Sheets("Completions").Select

[AB33]

Since this is a recorded macro and use lots of methods like "Selection, Activate", you will get an error now and then.
I am assuming the sheet you are selecting comes from the opened book.

Please Login or Register  to view this content.

[skate1991]

Hi AB33,

Thanks for that, that worked perfectly, my only problem now is when I want to jump back into that workbook it comes up with the run time errpor 9.

Please see the below code, I have highlighted the points it comes into the runtime error.

I would really appreciate it if you could spare some more time to have a look into this.

[AB33]

There is no need to jump back and forward if the set up is correct.
I am assuming that you are running the code from activeworkbook which is thisworkbook

Please Login or Register  to view this content.