Running a macro on all manuallly selected sheets

[mattxu2006]

I have a formatting macro that works on the active sheet. (I just go to the worksheet that I want to format and then run the macro) I would like to be able to select multiple sheets in a workbook and then have the macro work on all selected sheets.
I don't know how many sheets may be in the workbook and I do not know to which sheets the macro will be applied until selecting them manually.
Thanks for any guidance

 

[Jonmo1]

Can you post the macro you have?

 

[Jonmo1]

Here's a 'very' basic example, but implimenting this to your existing code depends on what that code looks like.
ws becomes an object variable representing each sheet you have selected.
Any commands you have manipulating cells in your existing code, you add ws. to it, as below.

Dim ws As Worksheet
For Each ws In ActiveWindow.SelectedSheets
    ws.Range("A1").Value = "Hello"
Next ws

 

[JoeMo]

Depending on what your formatting code looks like you could do something like this:

Dim sht As Worksheet
Sub AllSelectedSheets()
Application.ScreenUpdating = False
For Each sht In ActiveWindow.SelectedSheets
    Call myMacro
Next sht
Application.ScreenUpdating = True
End Sub
Sub myMacro()
With sht 'your desired formatting here
    .UsedRange.Columns.ColumnWidth = 20
    .UsedRange.Cells.Font.Size = 14
End With
End Sub

[/code]

 

[mattxu2006]

sorry for the slow response.
For the most part, this is the answer. Thank you.

However, I am running into an issue with trying to apply protection to the worksheet.

Dim wS As Worksheet
Dim myPassword As String
    
myPassword = "" 'Remove password (i.e. make "") to have no password
For Each wS In ActiveWindow.SelectedSheets
With wS
    
    .Protect Password:=myPassword, _
    Contents:=True, _
    AllowFormattingCells:=True, _
    AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, _
    AllowInsertingColumns:=True, _
    AllowInsertingRows:=True, _
    AllowInsertingHyperlinks:=True, _
    AllowDeletingColumns:=True, _
    AllowDeletingRows:=True, _
    AllowSorting:=True, _
    AllowFiltering:=True, _
    AllowUsingPivotTables:=True, _
    DrawingObjects:=True, _
    Scenarios:=False, _
    UserInterfaceOnly:=True 'Note—this statement and the next are necessary to allow grouping.
    .EnableOutlining = True
    .EnableSelection = xlNoRestrictions 'Set to xlNoRestrictions or xlUnlockedCells or xlNoSelection
    End With
Next wS

Error Code 1004
Method 'Protect' of '_worksheet' failed

I know that is an issue with identifying the correct object, but that is as far as I understand.

 

[Jonmo1]

When you wrote this in thee

myPassword = "" 'Remove password (i.e. make "") to have no password

That to me implies that the sheet is 'Already' protected.
Therefore you must first UNprotect the sheet with the current password
THEN you can ReProtect it with "" as the password.

So the first line after With

With wS
    .UnProtect Password:="[COLOR=#FF0000]CurrentPasswordHere[/COLOR]"
    .Protect Password:=myPassword,

 

[mattxu2006]

When you wrote this in thee


That to me implies that the sheet is 'Already' protected.
Therefore you must first UNprotect the sheet with the current password
THEN you can ReProtect it with "" as the password.

So the first line after With

With wS
    .UnProtect Password:="[COLOR=#ff0000]CurrentPasswordHere[/COLOR]"
    .Protect Password:=myPassword,

That piece of text should not be consequential as it has ' in front. it is meant as a marker. currently the password is = "" and this is a place to change that.

the issue appears to be that the code does not see the worksheet as an object

 

[mattxu2006]

Found this thread:
https://www.mrexcel.com/forum/excel...multiple-sheets.html?highlight=selectedsheets

Thank you JonmMo1 and JoeMo. your suggestions pointed me this way.