Skip running VBA depending on sheet name

[tlc53]

Hi there,

I have the following code which runs on every sheet of my spreadsheet and there are 167 sheets in total;

Sheets(Range("A164").Value).Select
ClientNarrative
HideRows
PrintSetup
Sheets(Range("A165").Value).Select
ClientNarrative
HideRows
PrintSetup
Sheets(Range("A166").Value).Select
ClientNarrative
HideRows
PrintSetup

The sheets/tabs are named 3x letters and 2x numbers - eg. ABC01, ZZZ03. To speed it up slightly, I would like to add that if the Sheet name starts with "ZZZ" don't run ClientNarrative, HideRows or PrintSetup, instead stop skip to the next sheet.

Can someone help me write the code that needs to go between, eg. "Sheets(Range("A164").Value).Select" and "ClientNarrative"?

Thank you!

 

[Zot]

Maybe something structured like this

Sub test()

Dim cell As Range, rngShtName As Range

Application.ScreenUpdating = False

Set rngShtName = Range("A164", "A166")

For Each cell In rngShtName
    If Not Left(cell.Text, 3) = "ZZZ" Then
        Sheets(cell.Text).Activate
        ClientNarrative
        HideRows
        PrintSetup
    End If
Next

End Sub

 

[tlc53]

Maybe something structured like this

Maybe something structured like this

Thank you! That looks much simpler. I hadn't thought it could be restructured like that, so I didn't post all of the VBA code.
I have now tried to incorporate your code into the existing code. However, I now know it's not as easy as just doing that!
It won't run, and highlights the first row of code as an error.

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
UnprotectAll
Sheets("Invoice Data").Select
CleanUp

Dim cell As Range, rngShtName As Range

Application.ScreenUpdating = False

Set rngShtName = Range("A112", "A113", "A114", "A115", "A116", "A117", "A118", "A119", "A120", "A121", "A122", "A123", "A124", "A125", "A126", "A127", "A128", "A129", "A130", "A131", "A132", "A133", "A134", "A135", "A136", "A137", "A138", "A139", "A140", "A141", "A142", "A143", "A144", "A145", "A146", "A147", "A148", "A149", "A150", "A151", "A152", "A153")

For Each cell In rngShtName
    If Not Left(cell.Text, 3) = "ZZZ" Then
        Sheets(cell.Text).Activate
        ClientNarrative
        HideRows
        PrintSetup
    End If
Next

Sheets("Dashboard").Select
Point3Format
ProtectAll
Sheets("Dashboard").Select
Application.ScreenUpdating = True
CommandButton2.Enabled = False
End Sub

 

[Zot]

I believe the error is due to this statement

Set rngShtName = Range("A112", "A ......

The problem is you have many sheet and the Range has no reference to any. More proper way to write code

Private Sub CommandButton2_Click()

Application.ScreenUpdating = False
UnprotectAll
Sheets("Invoice Data").Select
CleanUp

Dim cell As Range, rngShtName As Range
Dim wsA As Worksheet

Application.ScreenUpdating = False

Set wsA = ActiveWorkbook.Sheets("Name")

Set rngShtName = wsA.Range("A112", "A153")

For Each cell In rngShtName
    If Not Left(cell.Text, 3) = "ZZZ" Then
        Sheets(cell.Text).Activate
        ClientNarrative
        HideRows
        PrintSetup
    End If
Next

Sheets("Dashboard").Select
Point3Format
ProtectAll
Sheets("Dashboard").Select
Application.ScreenUpdating = True
CommandButton2.Enabled = False

End Sub

You don't need to list down all addresses to define range, but just put the 1st and last cell to define it as above.
Instead of using Sheets("name"), it is much easier to define like above. I use wsA to define ActiveWorkbook.Sheets("Name"). Using something recognizable is even better like wsName. I'm assuming that you have a worksheet called Name where you list all the sheet names in range.

You can define Sheets("Dashboard") like wsDash, but remember to declare in DIM. This can speed up execution a bit since program no need to re-read the sheet name again and again. In your code, you select (or activate) the sheet that you need to run subroutine because I believe your subroutine has only Range("A blah..blahh.. . The range will refer to active sheet (the reason for selecting/activating the sheet). The best way is to pass the sheetname as parameter into subroutine if you define sheet name. For example:

For Each cell In rngShtName
    If Not Left(cell.Text, 3) = "ZZZ" Then
        Set ws = ActiveWorkbook.Sheets(cell.Text)
'        Sheets(cell.Text).Activate
        Call ClientNarrative(ws)
        Call HideRows(ws)
        Call PrintSetup(ws)
    End If
Next

This way you passed sheet name through defined ws. Of course your subroutine has to accept the parameter as well like

Sub ClientNarrative(ws as Worksheet)
(your code)
End Sub

You can google passing parameter to subroutine. By doing so, you I can remove line Sheets(cell.Text).Activate. Code will run faster and more efficient.

You need to modify my code above to suite your workbook as I don't know where the sheet name list resides.

Ohh.. I forgot that I use Call ... there is difference if you use Call or not using it when calling subroutine. Refer here

Call statement (VBA)

Office VBA reference topic

docs.microsoft.com

 

[tlc53]

I believe the error is due to this statement

Thank you for all that information. I have been playing around with this code for sometime.
If my rngShtName = wsA.Range does not contain an account with ZZZ it runs as expected.
As soon as I put a ZZZ account in (in sample below, that is cell A34) it comes back with a Run-time error '450': Wrong number of arguments or invalid property assignment. Debug then highlights row: Set rngShtName = wsA.Range("A12", "A13", "A34")

I did trying setting the ws code but I found the below code worked best. I could be wrong, but I think the error lies around this code - If Not Left(cell.Text, 3) = "ZZZ" Then
What do you think?

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
UnprotectAll
Sheets("Invoice Data").Select
CleanUp

Dim cell As Range, rngShtName As Range

Application.ScreenUpdating = False

Set wsA = ActiveWorkbook.Sheets("Dashboard")

Set rngShtName = wsA.Range("A12", "A13", "A34")

For Each cell In rngShtName
    If Not Left(cell.Text, 3) = "ZZZ" Then
        Sheets(cell.Text).Activate
        ClientNarrative
        HideRows
        PrintSetup
    End If
Next

Sheets("Dashboard").Select
Point3Format
ProtectAll
Sheets("Dashboard").Select
Application.ScreenUpdating = True
CommandButton2.Enabled = False
End Sub

 

[Akuini]

Debug then highlights row: Set rngShtName = wsA.Range("A12", "A13", "A34")

Do you mean you want to get range "A12", "A13" and "A34"? then it should be:
Set rngShtName = wsA.Range("A12,A13,A34")

 

[tlc53]

Do you mean you want to get range "A12", "A13" and "A34"? then it should be:
Set rngShtName = wsA.Range("A12,A13,A34")

Thank you!! That was the problem. It's working now

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.
I explain this a bit:
Range("A1", "A5") means range A1 to A5 so it's the same with Range("A1:A5")
Range("A1, A5") means range A1 and A5
Range("A1, A5, A8") means range A1 and A5 and A8
Range("A1:A5, A8") means range A1 to A5, and A8

Try this:

Sub try10()
Debug.Print Range("A1", "A5").Address
Debug.Print Range("A1, A5").Address
Debug.Print Range("A1, A5, A8").Address
Debug.Print Range("A1:A5, A8").Address
End Sub

result in immediate window:
$A$1:$A$5
$A$1,$A$5
$A$1,$A$5,$A$8
$A$1:$A$5,$A$8

 

[Zot]

Thanks @Akuini for helping.