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:
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:
I am using Excel 2007
Thanks for your time!
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
Thanks for your time!
Last edited: