How to change code to apply to all files

UzieJacuzzi

New Member
Joined
Jul 17, 2012
Messages
8
Hello everyone,

I have a macro i recorded for the following:

Sort
Autofit Contents
Align Left

Here is the code:

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:

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

I am using Excel 2007

:biggrin: Thanks for your time! :biggrin:
 
Last edited:

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

buxtongt

Well-known Member
Joined
Oct 5, 2011
Messages
709
Try using:

ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear

Hope this helps.
 

UzieJacuzzi

New Member
Joined
Jul 17, 2012
Messages
8
Worked perfect! I used your provided statement and plugged it into the code. Here is the amended code. Thank you so much!

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,621
Members
414,081
Latest member
Subaru_Steve

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