Mark has attended:
Excel VBA Intro Intermediate course
Excel Advanced - For Power Users course
Excel PowerPivot course
Excel Dashboards for Business Intelligence course
Sorting Data on Multiple Tabs
I've written the following sub routine to sort a data set on one tab and then move onto the next tab and sort another data set. The data set is the same size and takes up exactly the same cells on all tabs. It's sorting on one tab but not moving onto the next.
Help?
Sub Sort()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Range("A16:E163").Select
Range("A163").Activate
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("E16:E163"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A16:E163")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A4").Select
Next ws
End Sub
RE: Sorting Data on Multiple Tabs
Hi Mark,
Thank you for the forum question.
You are very very close.
You are only missing one thing in the code. You are not changing the active sheet. If you after the loop enter ws.select the code will run through all sheets in the workbook (see below).
Sub Sort()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Select
Range("b3") = 3
Range("A16:E163").Select
Range("A163").Activate
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("E16:E163"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A16:E163")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A4").Select
Next ws
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
