98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Sorting Data on Multiple Tabs
Sorting Data on Multiple Tabs
Resolved · High Priority · Version 2010
Fri 17 Apr 2015, 14:00Delegate Mark said...
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
Mon 20 Apr 2015, 20:39Trainer Jens said...
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
Mon 27 Apr 2015: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Outlining - ungrouping rows or columnsHighlight want you want to ungroup and press ALT + SHIFT + right cursor arrow |
