How to build a macro to sort data on every worksheet in EXcel?

  • Thread starter Thread starter Peter Liu
  • Start date Start date
P

Peter Liu

I was trying to build a macro to sort data on every worksheet within Excel
2007. However, after recording the macro, the macro seems to only refer to
the original worksheet. The macro does not work in other worksheet.

Here is the sequence I used:

1. Start recording new macro.

2. CTRL+Pagedonw to go to next worksheet.

3. Data-sort

4. Stop recording.

The above procedure works at the specific worksheet. However, when I run
the the macro, it does not sort the new worksheet.

By the way, I used the same procedure in Excel 2003. It worked just fine
then.
 
Thanks for the reply:

Here is the code:
--
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+w
'
ActiveSheet.Next.Select
Range("A49:K130").Select
ActiveWorkbook.Worksheets("Anes").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Anes").Sort.SortFields.Add
Key:=Range("B49:B130") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Anes").Sort.SortFields.Add
Key:=Range("C49:C130") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Anes").Sort
.SetRange Range("A49:K130")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A49").Select
End Sub

--

The problem seems to be from the "relative" reference.

I look forward to hearing from you.
 
You are telling it to sort worksheet Anes, each and every time. Try this
(untested)

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+w
'
Dim myWS as excel.worksheet

For Each myws In ThisWorkbook.Worksheets
myws.Sort.SortFields.Clear
myws.Sort.SortFields.Add _
Key:=Range("B49:B130") _
, SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
myws.Sort.SortFields.Add _
Key:=Range("C49:C130") _
, SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
myws.Sort
.SetRange myws.Range("A49:K130")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub
 
I appreciate your help. However, since I am not a VB programmer, I like to
use the macro recorder to complete the sort macro. Can you test the macro
recorder within Excel? Data sort was the only function that I could not
build in the macro. It seems to have something to do with the "Relative
Reference." But, I just could not make the macro work with data sort.

Thanks in advance.
 
Just replace "Worksheets("Anes")" everywhere in your macros on ActiveWorksheet, and macros will work on your current sheet!

good luck!
 
Back
Top