How to have user select which worksheets to run macro on

[bakom002]

I currently have vba code that runs through all of the named ranges in the workbook and for every sheet within an array of all the sheets in the work book it will find a specific named range and copy that data to different data dump location.


What I would like, is for the user to be able to select which sheets out of all the sheets in the workbook they want the code to run on specifically, instead of it running based on a defined/static array of sheets


Here is the part of the code that I would like to change:

Dim WshtNames As VariantDim WshtNameCrnt As Variant
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim UpldNames As Variant
Dim UpldNameCrnt As Name




''''Determine applicable tabs. 
WshtNames = list of tabs with yellow boxes for upload purposes
WshtNames = Array("NewShare", "1590_Migration Depts", "PrjSvc", "Talent Mgmt CoE", "COS Intra-COS Alloc", "ADP RM", "Benefits CoE", "TLMeTimeUsage", "BenefitsToCOS", "Division", "TLM_CoE", "TLM_Depreciation", "FulfAllocSHPS", "FulfAllocNonSHPSBen", "ProB_COS", "PSC_COS", "COS_SASS")








For Each WshtNameCrnt In WshtNames
   With Worksheets(WshtNameCrnt)
   Worksheets(WshtNameCrnt).Activate
    
      For Each UpldNameCrnt In ActiveWorkbook.Names
         
         If Left(UpldNameCrnt.Name, 6) = "Upload" Then
            
              If UpldNameCrnt.RefersToRange.Parent.Name = ActiveSheet.Name Then
            
[THIS SECTION IS WHERE THE MAIN COPY/PASTE CODE GOES I LEFT IT OUT TO SAVE SPACE]             
              End If
          End If
       
       Next UpldNameCrnt

Thank you all in advanced for the assistance.

 

[bakom002]

Also preemptively, the first comment encompasses all of the following: "Determine applicable tabs. WshtNames = list of tabs with yellow boxes for upload purposes d purposes"

 

[Mentor82]

Hi,
It comes to my mind two options:
1. Make the code so it works on activesheet only and before updating ask user by msgbox for the decision if to run the macro on the active worksheet
2. Create a form with a combo button included. While loading the form, fill in the combo button with names of all worksheets in order to select. Once user selects the worksheet on which they would like to run the macro and press ok button process your code on that specific worksheet only

 

[bakom002]

Thanks for responding,
1. Is viable but that’s still a bit manual/time consuming on the end user
2. That was the option I was planning on exploring, but how would the user select more than just 1 sheet to run the macro on, since the combo field is a drop down?

 

[Mentor82]

Hi,
You can use list box instead plus multiselection option. Within Listbox you can process multiselection . Once user selects worksheets you have to check which options are selected using for/next systement and process the code for all selected worksheets - it's possible

 

[bakom002]

Thanks! I’m gonna look into that tomorrow, I will make a post here if I figure things out well enough

 

[mikerickson]

You could have the user select multiple tabs through the Excel interface then run a macro like

Dim oneSheet as Worksheet

For each oneSheet in ActiveWindow.SelectedSheets
    ' do macro on oneSheet
Next oneSheet

If you want to pause a master macro for the user to select multiple sheets, a userform with multiselect listbox would be one way to go.

 

[bakom002]

Hey I just wanted to respond to this and say that I figured it out! Using a multi selection list box and for/next statements to loop through the list of sheet names produced just like you suggested. Thanks!

 

[Mentor82]

My pleasure. I'm glad you've made it & thanks for your feedback