VB Macro question

A

apache007

Dear experts,

I have the following macro to sort Cells.

--------------------------------------------------------
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+x
'
Rows("4:385").Select
ActiveWorkbook.Worksheets("Aug").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Aug").Sort.SortFields.Add
Key:=Range("A4:A385"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Aug").Sort.SortFields.Add
Key:=Range("B4:B385"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Aug").Sort
.SetRange Range("A4:AD385")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
-------------------------------------

I would to change the constant "Aug" to the selected/active Sheet, so that I
wont have to change "Aug" all the time.

What should I change "Aug" to??

Thanks in advance.
 
J

Jacob Skaria

Replace
Worksheets("Aug")

with
ActiveSheet

to look like the below for all lines

ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
OR
ActiveSheet.Sort.SortFields.Clear

If this post helps click Yes
 
J

Joel

Since there may be a different number of rows in each worksheet this code is
simplier and more flexible.

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+x
'
With ActiveWorkbook.ActiveSheet
LastRow = .Range("A" & Rows.count).end(xlup).row
if Lastrow >= 4 then
.Rows("4:" & LastRow").Sort _
header:=xlguess, _
Key1:=Range("A4"), _
Order1:=xlAscending
end if
End With

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Automatic sort macro 2
range definition by cells numbers 3
Sort by column, not range 1
Modify Sort Routine to inlcude All Data 2
Undo Macro Action 3
Macro Help 3
Sorting Question 5
VBA 2 Codes 2

Top