Macro: Inserting formula on multiple worksheets

[mrjmeza]

Hello:

I have a workbook that has several spreadsheets. In some, but not all, of the worksheets in the workbook I need to add a formula to a specific column. I have a macro that will add the formula to all the worksheets but is there a way to omit a few of the worksheets from the macro?

 

[ACommandLineKindaGuy]

If you only want the formulas in the "Costs" and "Expenses" sheets, then skip all the others

For n = 1 To Worksheets.Count
   With Worksheets(n)
      If .Name = "Costs" or .Name = "Expenses" Then
          [add the formula code here]
      End If
   End With
Next n

 

[Alex Blakenburg]

So @ACommandLineKindaGuy has given you one option which is quite a common approach.
There are multiple ways of doing it all comes down to what is easier to maintain.
eg which is the longer list the exclusions or inclusions ?
or which is more likely to change ?
are there any patterns in the name that you can use to include or exclude ?

Here is a format I find easier for maintenance if you need to use specific names.
This lists Exclusions but you could reverse it to list inclusions.

Sub IncludeExcludSheets()
    Dim ws As Worksheet
    
    For Each ws In Worksheets
        Select Case ws.Name
            Case "this sheet", "that sheet"
                ' do nothing
                Debug.Print "Do nothing: "; ws.Name
            Case Else
                ' all the other sheets do something
                Debug.Print "Do something: "; ws.Name
        End Select
    Next ws
End Sub

 

[mrjmeza]

So @ACommandLineKindaGuy has given you one option which is quite a common approach.
There are multiple ways of doing it all comes down to what is easier to maintain.
eg which is the longer list the exclusions or inclusions ?
or which is more likely to change ?
are there any patterns in the name that you can use to include or exclude ?

Here is a format I find easier for maintenance if you need to use specific names.
This lists Exclusions but you could reverse it to list inclusions.

Sub IncludeExcludSheets()
    Dim ws As Worksheet
   
    For Each ws In Worksheets
        Select Case ws.Name
            Case "this sheet", "that sheet"
                ' do nothing
                Debug.Print "Do nothing: "; ws.Name
            Case Else
                ' all the other sheets do something
                Debug.Print "Do something: "; ws.Name
        End Select
    Next ws
End Sub

This should work! There are only three exclusions while several inclusions. Thanks!

 

[mrjmeza]

Just thinking it over, is there an option if the required tabs have a common identifier, such as the number 80 as a prefix?

 

[ACommandLineKindaGuy]

Well, yeah--you can test for anything in the worksheet name label. Just as you can test if there's some common characteristic of worksheets that do or don't get the formula inserted, such as Cell C1 is empty, or data starts on a different row, or even stuff like the sheet tab is a certain color.

You need to be a bit more specific if you want help in that regard!

 

[Alex Blakenburg]

In terms of converting what @ACommandLineKindaGuy has said into an example:
(you may not need the Else if there is no other code after the End If and before the Next ws

Sub SheetsWithPrefix()
    Dim ws As Worksheet
   
    For Each ws In Worksheets
        If ws.Name Like "80*" Then
            ' Sheet names that start with 80
            Debug.Print "Do something: "; ws.Name
        Else
            ' all other sheets do nothing
            Debug.Print "Do nothing: "; ws.Name
        End If
    Next ws
   
End Sub