Macro is choosing by tab name?

D

Dawn

Hi -- I have a macro I recorded that's doing some formatting on a report.
One part of the macro is looking at the name of the worksheet, but it's not
always the same name. Is there any way to alter this code so it works no
matter what the worksheet name is? It really just needs to select a
particular column, I'm not sure why it's looking at the worksheet name at
all. Thanks for any advice!

-Dawn

ActiveWorkbook.Worksheets("Docket_Report 1 ").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Docket_Report 1 ").Sort.SortFields.Add _
Key:=Range("H2"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption _
:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Docket_Report 1 ").Sort
.SetRange Range("A3:I45")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
 
J

Jim Thomlinson

If you intend the code to be run against the active worksheet then change...

ActiveWorkbook.Worksheets("Docket_Report 1 ")
to
ActiveWorksheet

If you intend to run it against a specific sheet whose tab name changes then
you will need to specify the code name of the sheet. We can help you with
that if you need...
 
D

Dawn

Hi Jim -- Thanks for your help! I am trying to get it to run against
whatever the current active sheet is. So, I tried to follow your email and I
changed to code to look like this:

Range("H2").Select
ActiveWorksheet.Sort.SortFields.Clear
ActiveWorksheet.Sort.SortFields.Add _
Key:=Range("H2"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption _
:=xlSortTextAsNumbers
With ActiveWorksheet.Sort
.SetRange Range("A3:I45")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

but now it gives me a new error message when I run it:

error 424 "object required". When I debug, it highlights the following line:

ActiveWorksheet.Sort.SortFields.Clear

Ideas? I'm using Excel 2008, if you think that might make any difference.

Thanks!
Dawn
 
Top