Run-Time Error '9'

[risscand]

Good Afternoon,

I'm relatively new to VBA and macros but I'm enjoying the journey. I am experiencing issues with a Run-Time error '9' in my macro "Subscript out of range." The debugger identified this line as the issue:

With ActiveWorkbook.Worksheets("Incoming_Request_Detail_data").Sort

After some research I have found that it may be attributed to using the shortkeys however running it manually via a customized ribbon did not work. The only thing missing from my macro when I run it is filtering the data to alphabetize it. Even though that is a simple step for the user to complete, I would rather my work be complete.

Looking forward to the advice!

Riss

 

[hiker95]

risscand,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


In order to assist you it would help us if you posted all of your macro code using code tags.

When posting VBA code, please use Code Tags - like this:

[code=rich]

'Paste your code here.

[/code]

 

[risscand]

Thanks for the information.

Windows 7 Professional, Excel 2013, on a PC.

With ActiveWorkbook.Worksheets("Incoming_Request_Detail_data").Sort

 

[hiker95]

risscand,

We need to see all of your macro code.

 

[risscand]

Ah, thanks

Sub PageCountMacro()
'
' PageCountMacro Macro
'
' Keyboard Shortcut: Ctrl+Shift+P
'
    Cells.Select
    Selection.Rows.AutoFit
    Selection.Columns.AutoFit
    Columns("B:I").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.SmallScroll ToRight:=0
    Columns("C:AC").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.SmallScroll ToRight:=1
    Columns("C:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:G").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Columns("C:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:K").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:H").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:H").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Columns("E:K").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.ScrollColumn = 1
    Columns("A:A").Select
    Selection.Cut
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollColumn = 2
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight
    Range("D5").Select
    ActiveWindow.ScrollColumn = 1
    Columns("C:C").Select
    Selection.Cut
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight
    Range("E4").Select
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("B:B").Select
    Selection.Cut
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight
    Range("E5").Select
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("B:B").Select
    With ActiveWorkbook.Worksheets("Incoming_Request_Detail_data").Sort
        .SetRange Range("A2:E1090")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C13").Select
    ActiveWindow.SmallScroll Down:=81
End Sub

 

[hiker95]

risscand,

Thanks for the recorded macro.

To start off, and, so that we can get it right on the first try:

Based on what your current macro is doing, it does not make sense to see actual screenshots.

I would like to see your actual raw data workbook/worksheet.

The following is a free site.

you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.