Ignore sheet in loop by cell or begining of sheet name?

[teatimecrumpet]

Hi,

I'm using the code below to loop through some sheets to do some formatting and copy/pasting to a new sheet. My question is: How can I modify it to ignore sheets if they have a particular string in a particular cell. Can I run another loop to change a sheet's name if it contains the string then modify the below to avoid sheets beginning with the new name (i.e. add an "N" to the begining of the sheet name if it has the cell string)?

Thanks in advance,

Dim WB As Workbook
Dim WS As Worksheet
Dim AW As String
AW = ActiveWorkbook.Path
Set WB = ActiveWorkbook
For Each WS In WB.Worksheets
Select Case LCase(WS.Name)
'Sheet names to exclude from export.
Case Is = "Blue", "Green", "Yellow"
Case Else
'formatting and pasting

End Select
Next WS

 

[njimack]

Something like this:

Dim WB As Workbook
Dim WS As Worksheet
Dim AW As String
AW = ActiveWorkbook.Path
Set WB = ActiveWorkbook
For Each WS In WB.Worksheets
    [COLOR="Red"]If WS.Range("A1") = "XXX" Then[/COLOR]
        Select Case LCase(WS.Name)
        'Sheet names to exclude from export.
        Case Is = "Blue", "Green", "Yellow"
        Case Else
        'formatting and pasting
        
        End Select
    [COLOR="Red"]End If[/COLOR]
Next WS
End Sub

 

[MrKowz]

What particular string in what particular cell?

 

[teatimecrumpet]

Mr. Kowz: The cell is in A4 and the string is "None".

njimack: That only seemed to work on the activesheet.


Thanks Guys

 

[MrKowz]

Something like this?

Public Sub TeaTimeCrumpet()
Dim WB As Workbook
Dim WS As Worksheet
Dim AW As String
AW = ActiveWorkbook.Path
Set WB = ActiveWorkbook
For Each WS In WB.Worksheets
    If WS.Range("A4").Value <> "None" Then
        Select Case LCase(WS.Name)
        'Sheet names to exclude from export.
        Case Is = "Blue", "Green", "Yellow"
        Case Else
        'formatting and pasting
        
        End Select
    Else
        'What to do when A4 = "None"
    End If
Next WS
End Sub

 

[teatimecrumpet]

It's still running only on the activesheet that is selected upon first running the macro.

Is having that part with the "Case Is= ..." causing a problem. From what I can understand in the code below.

If a cell in each worksheet is greater than or less than "None" It will run the part that selects the worksheets named "Blue" "Green" "Yellow" (which it'll do nothing because I want to exclude those sheets from any code. And for sheets not named those three above it will run the code that bolds the third line. And if a cell contains the string "None" it would highlight the 4th row.

But when I run the code below it's bolding both the 3rd and 4th rows for the active sheet regardless of it's name...

Dim WB As Workbook
Dim WS As Worksheet
Set WB = ActiveWorkbook
For Each WS In WB.Worksheets
If WS.Range("A4").Value <> "None" Then
Select Case LCase(WS.Name)
'Sheet names to exclude from export.
Case Is = "Blue", "Green", "Yellow"
Case Else
'formatting and pasting
Rows("3:3").Select
Selection.Font.Bold = True
End Select
Else
Rows("4:4").Select
Selection.Font.Bold = True
End If
Next WS

 

[njimack]

You need to qualify your ranges...

Dim WB As Workbook
Dim WS As Worksheet
Set WB = ActiveWorkbook
For Each WS In WB.Worksheets
If WS.Range("A4").Value <> "None" Then
Select Case LCase(WS.Name)
'Sheet names to exclude from export.
Case Is = "Blue", "Green", "Yellow"
Case Else
'formatting and pasting
[COLOR="Red"]Ws[/COLOR].Rows("3:3").Font.Bold = True
End Select
Else
[COLOR="Red"]ws.[/COLOR]Rows("4:4").Font.Bold = True
End If
Next WS

 

[MrKowz]

You need to qualify each of those ranges for ws:

Public Sub TeaTimeCrumpet()
Dim WB As Workbook
Dim ws As Worksheet
Set WB = ActiveWorkbook
For Each ws In WB.Worksheets
    If ws.Range("A4").Value <> "None" Then
        Select Case LCase(ws.Name)
        'Sheet names to exclude from export.
        Case Is = "Blue", "Green", "Yellow"
        Case Else
            'formatting and pasting
            ws.Rows("3:3").Font.Bold = True
            End Select
    Else
        ws.Rows("4:4").Font.Bold = True
    End If
Next ws
End Sub

Here is some code that will likely run much faster:

Public Sub TeaTimeCrumpet()
Dim ws          As Worksheet, _
    sheetarray  As Variant
    
sheetarray = Array("Blue", "Green", "Yellow")
For Each ws In ActiveWorkbook.Worksheets
    With ws
        If .Range("A4").Value <> "None" And IsError(Application.Match(.Name, sheetarray, 0)) Then
            .Rows(3).Font.Bold = True
        Else
            .Rows(4).Font.Bold = True
        End If
    End With
Next ws
End Sub

 

[teatimecrumpet]

Thanks guys. You were right about qualifying my ranges.

For everyone else here is my code. This will exclude three named sheets and any sheets with the word "none" in cell A9 from having the third row bolded. But if "none" is in cell A9 the fourth line will be bolded. The three named sheets will have no formatting done to it:

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook

For Each ws In wb.Worksheets
If ws.Range("A9").Value <> "None" Then
Select Case LCase(ws.Name)
'Sheet names to exclude from bolding
Case Is = "blue", "green", "yellow"
Case Else
'formatting and pasting
ws.Rows("3:3").Font.Bold = True
End Select
Else
ws.Rows("4:4").Font.Bold = True
End If
Next ws