Loop Record 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 in advance.

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

Sub ShootSort()
'
' ShootSort Macro
‘
'

  Range("E8:J27").Select

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

  ActiveWorkbook.Worksheets("Booking").Sort.SortFields.Add Key:=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

 

[Hui]

Do you want to run the same macro on all worksheets in the same workbook ?

Does each worksheet have data in the same range ?

If not do they all start in E8 ?

A bit more clarity about what is required would be nice

Can you post a sample file ?

 

[Scott_86_]

Hui, thanks for your reply.

Yes, same macro on all worksheets within the workbook.

Yes, each worksheet has the same data in the same range with the exact same layout.

Another member in another forum has posted a code which has worked.

In future, I will attempt to add more clarity and attach a sample file.

Scott

 

[vletm]

Scott_86_
Test next ...

Sub ShootSort()
    Application.ScreenUpdating = False
    On Error Resume Next
    For ws = 1 To Sheets.Count
        With Sheets(ws)
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=Range("J8:J27"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Sort.SortFields.Add Key:=Range("E8:E27"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With .Sort
                .SetRange Range("E8:J27")
                .Header = xlGuess
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With
    Next ws
    Application.ScreenUpdating = True
    MsgBox "ShootShort Done"
End Sub

 

[p45cal]

cross posted http://www.vbaexpress.com/forum/showthread.php?63739-Loop-record-macro-though-all-worksheets

 

[AlanSidman]

crossposted: https://www.mrexcel.com/forum/excel...op-recorded-macro-through-all-worksheets.html