How to change code to apply to all files

[UzieJacuzzi]

Hello everyone,

I have a macro i recorded for the following:

Sort
Autofit Contents
Align Left

Here is the code:

Sub Uzie_2_SortTidyUp()
'
' Uzie_2_SortTidyUp Macro
' Sort: (Smallest to Largest) Phys Sample Insp. Method Insp Counter  Autofit Contents, Align Left
'
'
    ActiveWorkbook.Worksheets("[COLOR=#ff0000]CondensedFile2InspLots[/COLOR]").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("[COLOR=#ff0000]CondensedFile2InspLots[/COLOR]").Sort.SortFields.Add Key:= _
        Range("N2:N69"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("[COLOR=#ff0000]CondensedFile2InspLots[/COLOR]").Sort.SortFields.Add Key:= _
        Range("E2:E69"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("[COLOR=#ff0000]CondensedFile2InspLots[/COLOR]").Sort.SortFields.Add Key:= _
        Range("BF2:BF69"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("[COLOR=#ff0000]CondensedFile2InspLots[/COLOR]").Sort
        .SetRange Range("A1:BF69")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Cells.Select
    Cells.EntireColumn.AutoFit
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

I would like to take this code and make it applicable to any file I place in there. If you look at the red text I highlighted in the code, it looks like it is taking the file name into account and if I try to run this macro on files with a different name, I get an error message stating:

"Run-time error '9'
Subscript out of range

Debug highlights the following piece of code:

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

I am using Excel 2007

Thanks for your time!

 

[buxtongt]

Try using:

ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear

Hope this helps.

 

[UzieJacuzzi]

Worked perfect! I used your provided statement and plugged it into the code. Here is the amended code. Thank you so much!

Sub Uzie_2_SortTidyUp()
'
' Uzie_2_SortTidyUp Macro
'
'
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
Range("N2:N69"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
Range("E2:E69"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
Range("BF2:BF69"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("A1:BF69")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Cells.Select
Cells.EntireColumn.AutoFit
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub

 

[buxtongt]

Glad it did the trick for you. Thanks for the feedback.