VBA Excel - exclude some worksheets from the loop not working-CodePudding

I would like to exclude some worksheets from my loop in Excel VBA.

I used the following hint: https://superuser.com/questions/1299900/exclude-sheets-when-looping-through-sheets

My code looks like this:

 Dim ws As Worksheet

 Set ws = Application.Worksheets.Count
  For i = 1 To a
   If ws.Name <> "BoQ" And ws.Name <> "Sign Off Sheet" And ws.Name <> "PIANOI" Then
     Worksheets(i).Cells(46, 14).Formula = "=Frontsheet!J10"
     Worksheets(i).Cells(46, 16).Formula = "=Frontsheet!J9"
   End If
 Next

But I am getting an error:

Type mismatch

tried to solve it by using this hint:

VBA Compile Error Type Mismatch - Looping with Sheet Function

but in vain.

How can I exclude some worksheets here?

CodePudding user response：

That's a real mishmash of codes. :)

Use ws as the loop variable in a For Each loop:

 Dim ws As Worksheet


  For Each ws in Activeworkbook.worksheets
   If ws.Name <> "BoQ" And ws.Name <> "Sign Off Sheet" And ws.Name <> "PIANOI" Then
     ws.Cells(46, 14).Formula = "=Frontsheet!J10"
     ws.Cells(46, 16).Formula = "=Frontsheet!J9"
   End If
 Next

CodePudding user response：

One of the arts of writing code is to relocate boilerplate code so that you can focus on the logic you want to implement. @Rory has shown you how to remove boilerplate around iterating a collection

The code below shows the use of an ArrayList to avoid multiple clauses in an If Statement.

Option Explicit

' Put the following in the module where you keep global variables
Const ExcludeNames As String = "BoQ,Sign Off Sheet,PIANOI"

' use this function to create the ArrayList of names you wish to exclude
' ArrayList requires a reference to mscorlib
Public Function SetupExcludedWorksheets(ByVal ipStringOfNames As String) As ArrayList

    Dim myName As Variant
    Dim myExcludes As ArrayList
    Set myExcludes = New ArrayList
    
    For Each myName In ipStringOfNames.Split(",")
        myExcludes.Add myName
    Next
    
    Set SetupExcludedWorksheets = myExcludes(excludeNames)
        
End Function

' in your setup routines include the following lines
Dim myExcludes As ArrayList
Set myExcludes = SetupExcludedWorksheets(ExcludeNames)

' Your code now becomes
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    If Not myExcludes.contains(ws.Name) Then
        ws.Cells(46, 14).Formula = "=Frontsheet!J10"
        ws.Cells(46, 16).Formula = "=Frontsheet!J9"
    End If
Next

You could, of course, revise the above code to have a list of only those names you wish to process.

CodePudding user response：

Copy to Multiple Worksheets With Exclusions

My Vision

Sub CopyFormulae()
    
    Const WORKSHEET_EXCLUSIONS_LIST As String _
        = "BoQ,Sign Off Sheet,PIANOI" ' maybe you want to add 'Frontsheet'?

    Dim WorksheetExclusions() As String
    WorksheetExclusions = Split(WORKSHEET_EXCLUSIONS_LIST, ",")
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet
    
    For Each ws In wb.Worksheets
        With ws
            If IsError(Application.Match(.Name, WorksheetExclusions, 0)) Then
                .Range("N46").Formula = "=Frontsheet!J10"
                .Range("P46").Formula = "=Frontsheet!J9"
            'Else ' is found in the WorksheetExclusions array; do nothing
            End If
        End With
    Next ws

    MsgBox "Formulae copied.", vbInformation

End Sub

Your Vision (In the Order of My Peference)

I prefer my idea because when I check the worksheet names with Application.Match, which is done case-insensitively i.e. A = a, I don't have to worry if I have misspelled a name in terms of case-sensitivity i.e. if I decide to rename the last worksheet to PianoI, I don't have to change it in the code like I would have to, using the following codes.
Study the If statement (it's the same in all three solutions) but study the rest harder.

Sub ForEachNext()
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet

    For Each ws In wb.Worksheets
        With ws
            If .Name <> "BoQ" And .Name <> "Sign Off Sheet" _
                    And .Name <> "PIANOI" Then
                .Range("N46").Formula = "=Frontsheet!J10"
                .Range("P46").Formula = "=Frontsheet!J9"
            End If
        End With
    Next ws
End Sub

Sub ForNext()
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim wsCount As Long: wsCount = wb.Worksheets.Count
    Dim ws As Worksheet
    Dim n As Long
    
    For n = 1 To wsCount
        Set ws = wb.Worksheets(n)
        With ws
            If .Name <> "BoQ" And .Name <> "Sign Off Sheet" _
                    And .Name <> "PIANOI" Then
                .Range("N46").Formula = "=Frontsheet!J10"
                .Range("P46").Formula = "=Frontsheet!J9"
            End If
        End With
    Next n
End Sub

Sub ForNextFewerVariables()
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim n As Long
    
    For n = 1 To wb.Worksheets.Count
        With wb.Worksheets(n)
            If .Name <> "BoQ" And .Name <> "Sign Off Sheet" _
                    And .Name <> "PIANOI" Then
                .Range("N46").Formula = "=Frontsheet!J10"
                .Range("P46").Formula = "=Frontsheet!J9"
            End If
        End With
    Next n
End Sub