Macro: Inserting formula on multiple worksheets

mrjmeza

New Member
Joined
Feb 9, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
If you only want the formulas in the "Costs" and "Expenses" sheets, then skip all the others
VBA Code:
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
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
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.

VBA Code:
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!
 
Upvote 0
Just thinking it over, is there an option if the required tabs have a common identifier, such as the number 80 as a prefix?
 
Upvote 0
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!
 
Upvote 0
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

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top