Why wont this macro work?

[Amstrad]

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:

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

 

[Amstrad]

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]

Something like this:

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]

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]

Sorry - that should be:

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

 

[Amstrad]

Works fine now. thanks for your help