code to run macro on selected sheet

[FMW]

Hello,
Everyday I received daily sales with a an excel sheet of anywhere from 10 to >100 job numbers. 1x per week I enter the necessary informtion into our invoicing program.
One of the steps I have to do first is sort each sheet by job number, customer, material. Then I also group customers (subtotal function).

I have macro for sorting and subtotal function. However, I cannot figure out code to run the macro on selected sheet. Currently, for example, my macro is:

 

[mse330]

Hello, I think your post is missing the macro … You can edit it before 10 mins of your post time. Insert the code then highlight it then press the <vba/> button

 

[FMW]

Yes...I guess my fingers moved a lot quicker than my brain. I have 7 sheets. Going into each differently named sheet and sorting "manually" is time consuming. Changing the sheet name of the below macro is not ideal either. I'm relatively new to macros, but am ambitious enough to want to learn how to execute these repetitive tasks. Thank you for your kind help.


Sub SortSalesDetail()

'

' SortSalesDetail Macro

' Sort Sales Detail


Range("A1:N55").Select

ActiveWorkbook.Worksheets("2020-05-12_Tickets").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("2020-05-12_Tickets").Sort.SortFields.Add2 Key:= _

Range("E2:E55"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

xlSortNormal

ActiveWorkbook.Worksheets("2020-05-12_Tickets").Sort.SortFields.Add2 Key:= _

Range("H2:H55"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

xlSortNormal

With ActiveWorkbook.Worksheets("2020-05-12_Tickets").Sort

.SetRange Range("A1:N55")

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

End Sub

 

[mse330]

Do you want to run the macro in all sheets of the workbook or certain sheets ?

 

[FMW]

Certain sheets: If I get days 1 and 2 sales reports, I could go ahead and sort those. Sometimes the reports are sent to me all at once for the entire week, and other times, maybe 1 report a day (the sender of the reports is not consistent).

 

[mse330]

Is there anyway to identify the sheets ? Or identify the sheets where you don't want to apply the sorting on

 

[FMW]

Each sheet is named with the date_Ticket, eg: 2020-05-12_Tickets, 2020-05-13_Tickets, and so forth. I could identify sheets already sorted somehow--perhaps change the sheet name, add an "S" to the name?

 

[Michael M]

Would it matter if they all got sorted, even the ones that have alreasy been done ??

Sub SortSalesDetail()
Dim ws As Worksheet
For Each ws In Worksheets
Range("A1:N55").Select
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add2 Key:=Range("E2:E55"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ws.Sort.SortFields.Add2 Key:=Range("H2:H55"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange Range("A1:N55")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next ws
End Sub

 

[FMW]

It would matter, because if I've already sorted a worksheet, I've also subtotalled (grouped) by customer and material.
Darn!

 

[Michael M]

Ok, is there an identifier in any cell of the sorted sheets, if so we could use that Identifier to skip the sheet