VB Macro question

  • Thread starter Thread starter apache007
  • Start date Start date
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.
 
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
 
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

writing a sort macro 2
Applying Variables to SORT 4
Automatic sort macro 2
Macro Help 3
Sort by column, not range 1
Undo Macro Action 3
range definition by cells numbers 3
VBA 2 Codes 2

Back
Top