Running the Same Macro on Multiple Worksheets and Multiple Files

[stroffso]

I have a piece of code called "restoredataval" which i need to apply to certain tabs in about 15 workbooks all of which have a varying number of tabs.

What i want to do is open the file, select the tabs I need the macro to run on and then press run. The macro should then Run through each tab selected one by one. For context the macro is restoring data validation settings to each of the tabs selected. Also the tab names on each workbook are vastly different so I cant specify arrays to run it on however a list of all the tab names I need are on a tab called Controls if that helps

Thanks in advance

 

[Zot]

1) Create a new workbook. This will be macro workbook where you run your macro from.
2) Create a userform with a CommandButton (I use default name here UserForm1) to pause execution to you time to select tabs/sheets

The code for UserForm1 under CommandButton (I put caption Continue)click:

Private Sub CommandButton1_Click()
Unload Me
End Sub

The code under normal module:

Sub LoopSelectedSheets()

Dim wb As Workbook
Dim ws As Worksheet
Dim wsSelect As Object

' Select workbook
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx), *.xls; *.xlsx", Title:="Select a File")
If Fname = False Then Exit Sub                         'CANCEL is clicked
' Define workbook as wb when opening it
Set wb = Workbooks.Open(Filename:=Fname, UpdateLinks:=False)

UserForm1.Show vbModeless
Do While UserForm1.Visible = True
    DoEvents
Loop

Set wsSelect = ActiveWindow.SelectedSheets

For Each ws In wsSelect

    ' Call your macro here

Next ws

End Sub

When the code is run, you will be prompt to select a workbook. Once workbook opened, button Continue will pop-up which can easily moved (vbModeless). Thenselect all your tabs of interest and click continue to execute your remaining code.

Opss. Look like I have no use of wb parameter but leave it there in case you need to call wb name ?

 

[stroffso]

Thats great thank you, for some reason it keeps erroring at the below row but only after I have selected multiple tabs and continued on the userform. If I run this one tab at a time it works fine

   Range("D6:D7").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="='Project Drops'!$BB$2:$BB$23"

 

[Zot]

It is important to specify the worksheet you are working on, else macro can get confused.
Instead of
Range("D6:D7").Select
try
ws.Range("D6:D7").Select

this will specify range on which sheet, not any other sheets. I think this will solve the problem

 

[stroffso]

Of course, thank you so much, still errored but I think i know what to do from here thanks