Macro adjustment

[petersing089]

Hello: I have read some ways to make this work but when I try them it doesn't. I want the macro below to be part of my file, then weekly I have to save the data into a new file and the tab where this data is located gets a new name. So I would like the macro to work on the active sheet, but it is looking for the old sheet name. Seems so simple but I am not a macro enabled person! See below. I want the macro to work on the active sheet regardless of workbook or sheet name.

Sub DivisionSort()
'
' DivisionSort Macro
'
' Keyboard Shortcut: Ctrl+d
'
    ActiveCell.Offset(0, 3).Range("A1:H13").Select
    Selection.Cut
    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, -2).Range("A1:J13").Select
    ActiveWorkbook.Worksheets("14").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("14").Sort.SortFields.Add Key:=ActiveCell.Offset(0, _
        4).Range("A1:A13"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("14").Sort
        .SetRange ActiveCell.Range("A1:J13")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("14").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("14").Sort.SortFields.Add Key:=ActiveCell.Offset(0, _
        3).Range("A1:A13"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("14").Sort
        .SetRange ActiveCell.Range("A1:J13")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("14").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("14").Sort.SortFields.Add Key:=ActiveCell.Offset(0, _
        2).Range("A1:A13"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("14").Sort
        .SetRange ActiveCell.Range("A1:J13")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[JackDanIce]

Untested, but should work for the activesheet only, try:

Sub DivisionSort()
'
' DivisionSort Macro
'
' Keyboard Shortcut: Ctrl+d
'
    ActiveCell.Offset(0, 3).Range("A1:H13").Select
    Selection.Cut
    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, -2).Range("A1:J13").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.add key:=ActiveCell.Offset(0, _
        4).Range("A1:A13"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange ActiveCell.Range("A1:J13")
        .header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.add key:=ActiveCell.Offset(0, _
        3).Range("A1:A13"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange ActiveCell.Range("A1:J13")
        .header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.add key:=ActiveCell.Offset(0, _
        2).Range("A1:A13"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange ActiveCell.Range("A1:J13")
        .header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[petersing089]

Thanks JackDanIce! It worked.

 

[JackDanIce]

You're welcome.