Why wont this macro work?

Amstrad

New Member
Joined
Feb 17, 2011
Messages
23
I've created a number of macros in PERSONAL and all of these work on other workbooks as long as I have PERSONAL open.

For some reason though this macro that I recorded will not work. I keep getting the error message:

Run-time error '9':

Subscript out of range

Here is the macro I'm trying to use:

Code:
ub SortHorizontal()
'
' SortHorizontal Macro
'

'
    Range("B1").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear 
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:O6")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("B:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("B:E").EntireColumn.AutoFit
    Columns("C:K").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Broken Link 1"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Anchor Text 1"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Broken Link 2"
    Columns("C:C").Select
    Columns("C:C").EntireColumn.AutoFit
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Anchor Text 2"
    Columns("D:D").Select
    Columns("D:D").EntireColumn.AutoFit
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Broken Link 3"
    Columns("F:F").Select
    Columns("E:E").EntireColumn.AutoFit
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Anchor Text 3"
    Columns("G:G").Select
    Columns("F:F").EntireColumn.AutoFit
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Broken Link 4"
    Columns("G:G").Select
    Columns("G:G").EntireColumn.AutoFit
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Anchor Text 4"
    Columns("H:H").Select
    Columns("H:H").EntireColumn.AutoFit
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Broken Link 5"
    Columns("I:I").Select
    Columns("I:I").EntireColumn.AutoFit
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Anchor Text 5"
    Columns("K:K").Select
    Columns("J:J").EntireColumn.AutoFit
    Rows("1:1").Select
    Selection.Font.Bold = True
    Range("A3").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Cut
    Range("C2").Select
    ActiveSheet.Paste
    Columns("C:C").EntireColumn.AutoFit
    Range("B3").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Cut
    Range("D2").Select
    ActiveSheet.Paste
    Columns("D:D").EntireColumn.AutoFit
    Range("A4").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Cut
    Range("E2").Select
    ActiveSheet.Paste
    Columns("E:E").EntireColumn.AutoFit
    Range("B4").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Cut
    Range("F2").Select
    ActiveSheet.Paste
    Columns("F:F").EntireColumn.AutoFit
    Range("A5").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Cut
    Range("G2").Select
    ActiveSheet.Paste
    Columns("G:G").EntireColumn.AutoFit
    Range("B5").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Cut
    Range("H2").Select
    ActiveSheet.Paste
    Columns("H:H").EntireColumn.AutoFit
    Range("A6").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Cut
    Range("I2").Select
    ActiveSheet.Paste
    Columns("I:I").EntireColumn.AutoFit
    Range("B6").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Cut
    Range("J2").Select
    ActiveSheet.Paste
    Columns("J:J").EntireColumn.AutoFit
    Range("A3").Select
End Sub

When I click debug it highlights this part of the code:

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

Any ideas on what I can do to fix this?

Many thanks
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Amstrad

New Member
Joined
Feb 17, 2011
Messages
23
Ok, I changed the name of one of my sheets to Sheet 1 and it seems to work.

How can I get this to work on all sheets, irrespective of what the sheet is called?

Thanks for your help
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,334
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Something like this:
Code:
Sub SortHorizontal()
'
' SortHorizontal Macro
'

'
   Dim wks As Worksheet
   Set wks = ActiveWorkbook.ActiveSheet
   With wks
      With .Sort
         With .SortFields
            .Clear
            .Add Key:=.Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
         End With
         .SetRange wks.Range("A2:O6")
         .Header = xlNo
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
      End With
      .Columns("B:E").Delete Shift:=xlToLeft
      .Columns("B:E").EntireColumn.AutoFit
      .Columns("C:K").Delete Shift:=xlToLeft
      .Range("A1:J1").Value = Array("Broken Link 1", "Anchor Text 1", "Broken Link 2", "Anchor Text 2", _
                                    "Broken Link 3", "Anchor Text 3", "Broken Link 4", "Anchor Text 4", _
                                    "Broken Link 5", "Anchor Text 5")
      .Rows("1:1").Font.Bold = True
      .Range("A3").Cut .Range("C2")
      .Range("B3").Cut .Range("D2")
      .Range("A4").Cut .Range("E2")
      .Range("B4").Cut .Range("F2")
      .Range("A5").Cut .Range("G2")
      .Range("B5").Cut .Range("H2")
      .Range("A6").Cut .Range("I2")
      .Range("B6").Cut .Range("J2")
      .Columns("A:J").EntireColumn.AutoFit
   End With
End Sub
 

Amstrad

New Member
Joined
Feb 17, 2011
Messages
23
I tried that but now I get the following error:

Run-time error '438'

Object doesn't support this property or method

When I debug it highlights this:

.Add Key:=.Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Thanks for your help
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,334
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Sorry - that should be:
Rich (BB code):
.Add Key:=wks.Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 

Forum statistics

Threads
1,141,019
Messages
5,703,771
Members
421,315
Latest member
awaisnazir139

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top