Forum Discussion

waygar's avatar
waygar
Brass Contributor
Nov 10, 2022

To run a macro on another excel sheet with a different number of rows

Hi,

I recorded a macro but when I try to use it on another spreadsheet with a different number of rows I get Error 9. Help I am new to this.

Macros and VBA

76 Replies

    • waygar's avatar
      waygar
      Brass Contributor
      Nov 10, 2022
      Sub Clean_WG1()
      '
      ' Clean_WG1 Macro
      ' Unclassified Scheduled Materials New
      '

      '
      Columns("D:D").Select
      ActiveWorkbook.Worksheets(" ClassfctnRptNew1459866").Sort.SortFields.Clear
      ActiveWorkbook.Worksheets(" ClassfctnRptNew1459866").Sort.SortFields.Add2 Key _
      :=Range("D1:D4009"), SortOn:=xlSortOnValues, Order:=xlDescending, _
      DataOption:=xlSortNormal
      With ActiveWorkbook.Worksheets(" ClassfctnRptNew1459866").Sort
      .SetRange Range("A2:T4009")
      .Header = xlNo
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
      End With
      Columns("A:A").Select
      ActiveWorkbook.Worksheets(" ClassfctnRptNew1459866").Sort.SortFields.Clear
      ActiveWorkbook.Worksheets(" ClassfctnRptNew1459866").Sort.SortFields.Add2 Key _
      :=Range("A1:A4010"), SortOn:=xlSortOnValues, Order:=xlAscending, _
      DataOption:=xlSortNormal
      With ActiveWorkbook.Worksheets(" ClassfctnRptNew1459866").Sort
      .SetRange Range("A2:T4010")
      .Header = xlNo
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
      End With
      Columns("F:F").Select
      ActiveWorkbook.Worksheets(" ClassfctnRptNew1459866").Sort.SortFields.Clear
      ActiveWorkbook.Worksheets(" ClassfctnRptNew1459866").Sort.SortFields.Add2 Key _
      :=Range("F1:F4011"), SortOn:=xlSortOnValues, Order:=xlAscending, _
      DataOption:=xlSortNormal
      With ActiveWorkbook.Worksheets(" ClassfctnRptNew1459866").Sort
      .SetRange Range("A2:T4011")
      .Header = xlNo
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
      End With
      Rows("1:1").Select
      With ActiveWindow
      .SplitColumn = 0
      .SplitRow = 1
      End With
      ActiveWindow.FreezePanes = True
      Range("A1").Select
      ActiveSheet.Range("$A$1:$T$4009").RemoveDuplicates Columns:=Array(1, 5), _
      Header:=xlYes
      Range("G:G,H:H,J:J,N:N,O:O,Q:Q,R:R").Select
      Range("R1").Activate
      Selection.Delete Shift:=xlToLeft
      Cells.Select
      Selection.Columns.AutoFit
      Range("A1").Select
      End Sub
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Nov 10, 2022

        waygar 

        Try this much shorter version. It operates on the active sheet.

         

        Sub Clean_WG1()
    Range("A1").CurrentRegion.Sort Key1:=Range("F1"), Key2:=Range("A1"), Key3:=Range("D1"), Header:=xlYes
    Range("A1").CurrentRegion.RemoveDuplicates Columns:=Array(1, 5), Header:=xlYes
    Range("G1,H1,J1,N1,O1,Q1,R1").EntireColumn.Delete
    Cells.EntireColumn.AutoFit
    With ActiveWindow
        .SplitRow = 1
        .FreezePanes = True
    End With
End Sub

         

Resources