VBA Help: Loop thru each tab EXCEPT those that start with letters...

[losamfr17]

Good morning dear wizards.

Can I please get some guidance on this dilemma?

I want to run the code below ONLY on worksheets whose names DO NOT START with "ULT".
Some tab names are all letters, numbers, or both letters and numbers. Is there a way to do that please?

Sub UNprotect_All_Sheets()


    Dim ws As Worksheet
        
    For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect Password:="adminf18"
    Next ws
    
End Sub

 

[Hendy2322]

The below code should do the trick. This link explains what the Left function does.

Sub UNprotect_All_Sheets()


    Dim ws As Worksheet
       
    For Each ws In ActiveWorkbook.Worksheets
        If Left(ws.Name, 3) <> "ULT" Then
            ws.Unprotect Password:="adminf18"
        End If
    Next ws
    
End Sub

 

[losamfr17]

It still includes the tab names with ULT when I try it. Did it work for you?

 

[losamfr17]

This is not looping thru each tab. Any idea why?

Sub ETS_All_Sheets()


    Dim ws As Worksheet
       
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "ULT124" Then
            Range("M4").Value = "Maybe"
        End If
    Next ws
    
End Sub

 

[Fluff]

You need to qualify the range, otherwise it works on the active sheet

ws.Range("M4").Value = "Maybe"

 

[Hendy2322]

This is not looping thru each tab. Any idea why?

Sub ETS_All_Sheets()


    Dim ws As Worksheet
       
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "ULT124" Then
            Range("M4").Value = "Maybe"
        End If
    Next ws
    
End Sub

Are you including the Left Function?

If Left(ws.Name, 3) <> "ULT" Then
    ws.Range("A1").Value = 1
End If

 

[losamfr17]

You need to qualify the range, otherwise it works on the active sheet

ws.Range("M4").Value = "Maybe"

Awesome, it works now! Thank you.
How would you add the OR operator to the code, say you want to change tabs that don't start with "ULT" OR "HOME" OR "ROLL"?

 

[losamfr17]

VBA: If with OR code won't work - Please help

Hello,

Could someone please explain to me why this code won't work?
I want to tell the code to skip worksheets with names starting with "ULT" OR "HOME" OR other names as well.

Sub ETS_All_Sheets()


    Dim ws As Worksheet
       
    For Each ws In ActiveWorkbook.Worksheets
        If (Left(ws.Name, 3) <> "ULT" Or Left(ws.Name, 3) <> "HOME") Then
            ws.Range("M4").Value = "Oui"
        End If
    Next ws
    
End Sub

 

[Norie]

Re: VBA: If with OR code won't work - Please help

Try this.

Sub ETS_All_Sheets()


    Dim ws As Worksheet
       
    For Each ws In ActiveWorkbook.Worksheets
        If Not ws.Name Like "ULT*" And Not ws.Name Like "HOME*" Then
            ws.Range("M4").Value = "Oui"
        End If
    Next ws
    
End Sub

 

[losamfr17]

Re: VBA: If with OR code won't work - Please help

It actually worked like a charm, thank you very much!!
I've learning VBA and getting better thanks to people like you!