Loop recorded macro through all worksheets

[scott_86_]

Hi,

I am trying to loop a one worksheet (name of that worksheet was Booking)recorded macro through all the sheets in the workbook.

The recording was to sort data within a range alphabetically.
No security issues I am aware of.

If anyone could amened the following code to this, that'd be great! Thanks inadvance.

--------------------------------------------------------


Sub ShootSort()
'
' ShootSort Macro
‘
'

Range("E8:J27").Select

ActiveWorkbook.Worksheets("Booking").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Booking").Sort.SortFields.AddKey:=Range("J8:J27") _

, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets("Booking").Sort.SortFields.Add Key:=Range("E8:E27")_

, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Booking").Sort

.SetRange Range("E8:J27")

.Header = xlGuess

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

End Sub

 

[Michael M]

Maybe this

Sub MM1()
Dim WS      As Worksheet
For Each WS In Worksheets
    WS.Sort.SortFields.Clear
    WS.Sort.SortFields.Add Key:=Range("E8:E27"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With WS.Sort
        .SetRange Range("E8:E27")
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Next WS
End Sub

 

[scott_86_]

Michael,

Thanks very much, that worked great.

Is there anyway to then make an exception for a single worksheet not to be included in this loop?

I have a worksheet that comes up in in VBA as Sheet1 (Data) that I do not want the sorting code to be applied to.

Thanks in advance.

 

[Michael M]

If the sheet name is Data, try

Sub MM1()
Dim WS As Worksheet
For Each WS In Worksheets
    If WS.Name <> "Data" Then
        WS.Sort.SortFields.Clear
        WS.Sort.SortFields.Add Key:=Range("E8:E27"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With WS.Sort
            .SetRange Range("E8:E27")
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If
Next WS
End Sub

 

[scott_86_]

That worked, thanks again.

I've got another query.

I've googled and tried plenty of codes, but can't get it to work:

Do you have any idea how to make all worksheets in a workbook automatically set to a specific % zoom level when opening an Excel file?

Thanks.

 

[Michael M]

Use

Sub workbook_open()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Select
        ActiveWindow.Zoom = 110 'change to suit zoom required
    Next ws
End Sub

 

[Macropod]

Cross-posted at:
http://www.vbaexpress.com/forum/showthread.php?63739-Loop-record-macro-though-all-worksheets
https://chandoo.org/forum/threads/loop-record-macro-through-all-worksheets.39866/
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[Michael M]

Grrr...Thanks Paul......