How do I get a macro to ignore the worksheet name?

[haganator]

I'm using XP SP3 and XL07.

I'm writing a macro to clean up a spreadsheet, and one problem that I have is the macro is using the sheet name when sorting a column. The next time I run the macro, it will be on a sheet with a different name and it will fail. How do I get the macro to ignore the sheet name?

Also, how can I make sure that the cell range for the sort will fit the next sheet, whether there are more or less items to sort?

Thanks in advance,
Jeff


Here is the macro:

Sub sert_macro1()
'
' sert_macro1 Macro
'

'
Range("B:C,F:F").Select
Range("F1").Activate
ActiveWindow.LargeScroll ToRight:=1
Range("B:C,F:F,K:L,O:AJ").Select
Range("O1").Activate
Selection.Delete Shift:=xlToLeft
ActiveWindow.LargeScroll ToRight:=-1
Columns("H:H").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("G:G").EntireColumn.AutoFit
Range("D4").Select
Cells.Replace What:="Verizon_NE_", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("E:E").EntireColumn.AutoFit
Range("F2").Select
ActiveWindow.LargeScroll ToRight:=-1
Columns("E:E").Select
ActiveWorkbook.Worksheets("Sert_Search_11_5_20113_23_19PM ").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Sert_Search_11_5_20113_23_19PM ").Sort.SortFields. _
Add Key:=Range("E2:E15"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sert_Search_11_5_20113_23_19PM ").Sort
.SetRange Range("A1:I15")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("D11").Select
End Sub

 

[shg]

Try this:

Sub sert_macro1()
    Dim wks         As Worksheet
 
    Set wks = ActiveSheet
    With wks
        .Range("B:C,F:F,K:L,O:AJ").Delete
 
        .Cells.Replace What:="Verizon_NE_", _
                       Replacement:="", _
                       LookAt:=xlPart, _
                       MatchCase:=False
 
        .Columns("E:E").AutoFit
        .Columns("G:G").AutoFit
        .Columns("H:H").NumberFormat = "m/d/yyyy"
 
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("E2"), _
                             SortOn:=xlSortOnValues, _
                             Order:=xlAscending, _
                             DataOption:=xlSortNormal
        With .Sort
            .SetRange Range("A1:I15")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
    End With
End Sub

 

[haganator]

Thanks. It worked perfectly.

 

[shg]

You're welcome, glad it helped.