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

haganator

New Member
Joined
Aug 5, 2008
Messages
42
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Try this:

Code:
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
 
Upvote 0
You're welcome, glad it helped.
 
Upvote 0

Forum statistics

Threads
1,222,226
Messages
6,164,714
Members
451,912
Latest member
HMF009

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
Back
Top