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:
 

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
708
Office Version
  1. 365
Platform
  1. Windows
How about this ? Or you can adjust accordingly

VBA Code:
Sub SortSalesDetail()

' SortSalesDetail Macro
' Sort Sales Detail
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "####-##-##_Tickets" Then
        ws.[A1].CurrentRegion.Sort key1:=ws.[E1], Header:=xlYes, Key2:=ws.[H1], Header:=xlYes
    End If
Next

End Sub
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
708
Office Version
  1. 365
Platform
  1. Windows
perhaps change the sheet name, add an "S" to the name?

If this option works for you, you can add the below line before the End If line in the code in post # 11 & the macro will change the name once the sorting is completed so it doesn't get altered again by the macro

VBA Code:
ws.Name = ws.Name & "_s"
 

FMW

New Member
Joined
Apr 10, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I like that. I'll mark an identififer as "entered", which also alerts me to the fact that I've invoiced.
 

FMW

New Member
Joined
Apr 10, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I'm going with your adding the VBA code....."_s"
 

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
708
Office Version
  1. 365
Platform
  1. Windows
Glad it’s working out for you & happy to help :)
 

FMW

New Member
Joined
Apr 10, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
THANK YOU! It's working great. Appreciate you taking time on Sunday night to answer. My next attempt: macro to perform subtotal!
Wish me luck.
Good night and stay safe always.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,862
Messages
5,574,711
Members
412,613
Latest member
EFRATA
Top