Loop record macro though 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.AddKey:=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

[p45cal]

try:

Sub ShootSort()
For Each sht In ActiveWorkbook.Sheets
  With sht.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("J8:J27"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("E8:E27"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range("E8:J27")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
Next sht
End Sub

(untested)

[Paul_Hossler]

@scott_86_

I added CODE tags to your post to format the macro -- you can use the [#] incon to insert CODE and \CODE tags and paste your macro between

p45cal's macro will work, but if the data to be sorted on all sheets is NOT the same layout (i.e. not always J8:J27 and E8:E27) some generalization is required

[p45cal]

oh groan…
cross posted https://chandoo.org/forum/threads/lo...ksheets.39866/
wasted my time.

[Paul_Hossler]

@scott_86_ -- if you come back to read this, please read http://www.vbaexpress.com/forum/faq...._new_faq_item3

All forums like to know if questions have been posted on other forums so that people don't waste time answering questions that have already been answered

[macropod]

Also cross-posted at: https://www.mrexcel.com/forum/excel-...st5150761.html