Macro - use on one current worksheet (i.e generic name ref)

[1cyril1]

This is likely to be an easy challenge for most of you experts: I have a very helpful macro which I generated via "Record Macro". I want to be able to run it on ANY similarly structured worksheet. Alas, it appears to have the original sheet name embedded (see code below). Can I replace the sheet name (REPORT-Q1-2013) to have it auto reference whatever sheet I happen to be running it in i.e a generic sheet ref? Appreciate any help

End With
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("2:67").Select
ActiveWindow.SmallScroll ToRight:=3
ActiveWorkbook.Worksheets("REPORT-Q1-2013").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("REPORT-Q1-2013").Sort.SortFields.Add Key:= _
Range("E2:E67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
ActiveWorkbook.Worksheets("REPORT-Q1-2013").Sort.SortFields.Add Key:= _
Range("AB2:AB67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("REPORT-Q1-2013").Sort
.SetRange Range("A2:AF67")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

 

[njimack]

Try the below (untested) code. You generally don't need to select ranges to perform actions on them, and the macro recorder, while useful, generates a lot of redundant code.

With ActiveSheet
    With .Sort
        .SortFields.Clear
        .Sort.SortFields.Add Key:= _
        .Range("E2:E67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
        .Sort.SortFields.Add Key:= _
        .Range("AB2:AB67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A2:AF67")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
End Sub

 

[1cyril1]

Thanks njimack. I tried replacing my code with yours and hit a compile error "Expected End With" with 'End Sub' highlighted as below. The very top of the (full) macro starts with 'Sub' and macro name on Line1; I guess this is what it's linking back to. Apart from commenting out the replaced code and swopping yours in I didn't change any code. You may spot a glitch in below?

Finally, while I think of it, if this routine is limited to a range of rows I may be in trouble as other worksheets could have more (or less) rows! Probably better to select all rows and get the empty ones to the bottom - is that easy to provide for in the macro?


With ActiveSheet
With .Sort
.SortFields.Clear
.Sort.SortFields.Add Key:= _
.Range("E2:E67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
.Sort.SortFields.Add Key:= _
.Range("AB2:AB67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A2:AF67")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub
Range("C2").Select
End Sub