code to run macro on selected sheet

FMW

New Member
Joined
Apr 10, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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:
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
708
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 10, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 18, 2007
Messages
708
Office Version
  1. 365
Platform
  1. Windows
Do you want to run the macro in all sheets of the workbook or certain sheets ?
 

FMW

New Member
Joined
Apr 10, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 18, 2007
Messages
708
Office Version
  1. 365
Platform
  1. Windows
Is there anyway to identify the sheets ? Or identify the sheets where you don't want to apply the sorting on
 

FMW

New Member
Joined
Apr 10, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
19,494
Office Version
  1. 2013
Platform
  1. Windows
Would it matter if they all got sorted, even the ones that have alreasy been done ??
VBA Code:
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

New Member
Joined
Apr 10, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
It would matter, because if I've already sorted a worksheet, I've also subtotalled (grouped) by customer and material.
Darn!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,494
Office Version
  1. 2013
Platform
  1. Windows
Ok, is there an identifier in any cell of the sorted sheets, if so we could use that Identifier to skip the sheet
 

Watch MrExcel Video

Forum statistics

Threads
1,118,808
Messages
5,574,434
Members
412,592
Latest member
moonsugar
Top