Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I want to repeat this on all worksheets, without individually selecting each
sheet and running the macro. I want the macro to run on all sheets and stop at the end. I am using this on a weekly back order report, so the worksheet names change weekly, but the name of each column is the same from A:L. Please help, I can't seem to find info about this. Here is what I have found so far, it works for each sheet individually, but I want it to do all sheets at once. Sub SortWorksheets() Dim N As Integer Dim M As Integer Dim FirstWSToSort As Integer Dim LastWSToSort As Integer Dim SortDescending As Boolean Dim sh As Worksheet SortDescending = False If ActiveWindow.SelectedSheets.Count = 1 Then FirstWSToSort = 1 LastWSToSort = Worksheets.Count Else With ActiveWindow.SelectedSheets For N = 2 To .Count If .Item(N - 1).Index < .Item(N).Index - 1 Then MsgBox "You cannot sort non-adjacent sheets" Exit Sub End If Next N FirstWSToSort = .Item(1).Index LastWSToSort = .Item(.Count).Index End With End If For M = FirstWSToSort To LastWSToSort For N = M To LastWSToSort If SortDescending = True Then If UCase(Worksheets(N).Name) _ UCase(Worksheets(M).Name) Then Worksheets(N).Move Befo=Worksheets(M) End If Else If UCase(Worksheets(N).Name) < _ UCase(Worksheets(M).Name) Then Worksheets(N).Move Befo=Worksheets(M) End If End If Next N Next M For Each sh In ThisWorkbook.Worksheets Columns("A:L").Select Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Key3:=Range("H2"), Order3:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3 _ :=xlSortNormal Next End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How do I display repeating text between worksheets only once on ta | New Users to Excel | |||
How do i use the same name multiple times in repeating worksheets | Excel Discussion (Misc queries) | |||
Repeating some code on all worksheets | Excel Programming | |||
Repeating macro procedure on several worksheets | Excel Programming | |||
Repeating (Looping) a Macro | Excel Programming |