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/loop-record-macro-through-all-worksheets.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 (http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#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-questions/1072296-loop-recorded-macro-through-all-worksheets-post5150761.html