Runtime Error '9' Subscript out of range

[TylerV]

I'm receiving a Runtime Error '9' Subscript out of range when I try to run run the below code.

I've tried changed the

Range("A5").Select

but that didn’t help.

The code below was created using the macro recorder.
This is the part that is highlighted when debugging

Windows( _
        "Fulfillment_Weekly_Headcount" _
        ).Activate

 Sub Macro1()
    Range("E6:G6,E11:G26").Select
    Range("E11").Activate
    ActiveWindow.SmallScroll Down:=21
    Range("E6:G6,E11:G26,E31:G38").Select
    Range("E31").Activate
    ActiveWindow.SmallScroll Down:=6
    Range("E6:G6,E11:G26,E31:G38,E43:G43,E48:G48,E53:G53").Select
    Range("E53").Activate
    ActiveWindow.SmallScroll Down:=21
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-66
    Range("E13").Select
    Sheets("CR Sum").Select
    Range("A5").Select
    Workbooks.Open Filename:="c:\Reports\repory1__DB_Summary.csv"
    Range("A2").Select
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 53
    Range("A2:K1044").Select
    Selection.Copy
    Windows( _
        "Fulfillment_Weekly_Headcount" _
        ).Activate
    ActiveSheet.Paste
End Sub

 

[patel45]

maybe the file Fulfillment_Weekly_Headcount is not open

 

[HaHoBe]

Hi, TylerV,

add the file extension to the command, I assumed it to be xlsx:

Windows( _
        "Fulfillment_Weekly_Headcount.xlsx" _
        ).Activate

You should consider to set objects to the workbooks and worksheets to avoid jumping between the windows.

Ciao,
Holger

 

[TylerV]

Ciao and patel45, Thank you both for your help.</SPAN>

Ciao, I don’t see the difference. Can you help?</SPAN>

 

[HaHoBe]

Hi, TylerV,

your code

Windows("Fulfillment_Weekly_Headcount").Activate

Maybe it should be

Windows("Fulfillment_Weekly_Headcount.xlsx").Activate

Like stated I assumed the extension of the workbook to be xlsx, you would need to change this to suit.

Ciao,
Holger

 

[HaHoBe]

Hi, TylerV,,

maybe this code will do what you want (you should alter the name for the range to clear contents), it will work between the workbook with the code and the workbook that needs to be opened.

Sub Macro1a()

Dim wbThis As Workbook
Dim wbNew As Workbook

Set wbThis = ThisWorkbook

wbThis.Sheets("Sheet_To_Clear").Range("E6:G6,E11:G26,E31:G38,E43:G43,E48:G48,E53:G53").ClearContents

On Error Resume Next
Set wbNew = Workbooks.Open(Filename:="c:\Reports\repory1__DB_Summary.csv")
On Error GoTo 0
If Not wbNew Is Nothing Then
  wbNew.ActiveSheet.Range("A2:K1044").Copy wbThis.Sheets("CR Sum").Range("A5")
  wbNew.Close False
End If
Set wbNew = Nothing

Set wbThis = Nothing
End Sub

Holger

 

[TylerV]

Perfect. It worked

Thanks for your help