code will only clear one worksheet I need to have it clear multiple sheets within the range given.

[ShawnSPS]

Sub ClearContents()
Dim iterator As Long
Application.DisplayAlerts = False
For iterator = ThisWorkbook.Worksheets.Count To 1 Step -1
With ThisWorkbook.Worksheets(iterator)
If .CodeName <> "Znotes,ZShortcuts" Then Range("D3:AH31,D34:AH41").ClearContents

End With
Next iterator
Application.DisplayAlerts = True
End Sub

 

[rollis13]

You need to correctly reference the sheet's range since you aren't selecting or activating the sheet to be cleared:
If .CodeName <> "Znotes,ZShortcuts" Then .Range("D3:AH31,D34:AH41").ClearContents

Ps. by the way, is "Znotes,ZShortcuts" a sigle sheet name or should they be two sheets.

 

[ShawnSPS]

You need to correctly reference the sheet's range since you aren't selecting or activating the sheet to be cleared:
If .CodeName <> "Znotes,ZShortcuts" Then .Range("D3:AH31,D34:AH41").ClearContents

Ps. by the way, is "Znotes,ZShortcuts" a sigle sheet name or should they be two sheets.

Znotes and Zshortcuts are two seperate sheets, that I am trying try bypass as the code clears of the contents of the other sheets via the range I gave.

 

[rollis13]

Use this test instead:
If Not (.CodeName = "Znotes" Or .CodeName = "ZShortcuts") Then .Range("D3:AH31,D34:AH41").ClearContents

 

[ShawnSPS]

Use this test instead:
If Not (.CodeName = "Znotes" Or .CodeName = "ZShortcuts") Then .Range("D3:AH31,D34:AH41").ClearContents

I tried your code, it failed only because Znote and Zshortcuts were protected. once I unprotected the sheets with your code and what I added it works flawless now.

Sub ClearContents()
Dim iterator As Long
Application.DisplayAlerts = False
For iterator = ThisWorkbook.Worksheets.Count To 1 Step -1
With ThisWorkbook.Worksheets(iterator)
'If .CodeName <> "Znotes,ZShortcuts" Then Range("D3:AH31,D34:AH41").ClearContents
Worksheets("Znotes").Unprotect ("Pila1DA.#")
Worksheets("ZShortCuts").Unprotect ("Pila1DA.#")
If Not (.CodeName = "Znotes" Or .CodeName = "ZShortcuts") Then .Range("D3:AH31,D34:AH41").ClearContents

End With
Next iterator
Application.DisplayAlerts = True
End Sub

 

[rollis13]

It works even with protected sheets. Are you sure you got the exact codenames (ex. capital letters) ? they need to exactly match (strings in macro / sheet's codename).

To avoid issues you could use:
If Not (UCase(.CodeName) = UCase("ZNotes") Or UCase(.CodeName) = UCase("ZShortcuts")) Then .Range("D3:AH31,D34:AH41").ClearContents
so whatever you write, the comparison will be between all capital letters.

 

[ShawnSPS]

It works even with protected sheets. Are you sure you got the exact codenames (ex. capital letters) ? they need to exactly match (strings in macro / sheet's codename).

To avoid issues you could use:
If Not (UCase(.CodeName) = UCase("ZNotes") Or UCase(.CodeName) = UCase("ZShortcuts")) Then .Range("D3:AH31,D34:AH41").ClearContents
so whatever you write, the comparison will be between all capital letters.

yes I used Worksheets("Znotes").Unprotect ("Pila1DA.#") and Worksheets("ZShortCuts").Unprotect ("Pila1DA.#") in another macro so the spelling is correct and works perfectly. so with what I have currently now it is working as it should it clears all data in the range I gave except Znotes and ZShortcuts but that only happens if I unprotect those sheets when it is executed.

 

[rollis13]

Sorry, can't replicate your issue with protected sheets. The only way to error is when sheet codename (or if using sheetname) doesn't coincide.
Anyway, if it's okay for you, it's okay for me. Glad having been of some help .