Issue with VBA running on multiple worksheets

Worker8ee

New Member
Joined
Aug 8, 2018
Messages
28
Hey everyone, I am trying to run the following VBA for all sheets in my workbook but it is only processing the tab that is selected at the time the VBA starts. I suspect it is because I don't have it formatted properly since I recorded a macro to inform this VBA but I don't have enough experience to understand how it is formatted improperly. Any suggestions/help would be greatly appreciated!

VBA Code:
Sub PriorQuarterTotals ()     
Dim ws As Worksheet
     For Each ws In ActiveWorkbook.Worksheets
    Columns("Q:Q").Select
    Selection.Replace What:="#N/A", Replacement:="0", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A1").Select
    Next ws
 
    
 For Each ws In ActiveWorkbook.Worksheets
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=""***Prior Quarter Totals: $""&TEXT(ROUND(SUMIFS('[Inventory VBA.xlsm]2020 Q2 All'!R4C8:R60000C8,'[Inventory VBA.xlsm]2020 Q2 All'!R4C6:R60000C6,1,'[Inventory VBA.xlsm]2020 Q2 All'!R4C14:R60000C14,R4C14),2),""#,##0_ ;-#,##0 "")&""***"""
    Range("E2").Select
    Selection.Font.Bold = True
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=""***Prior Quarter Retail Totals: $""&TEXT(ROUND(SUMIFS('[Inventory VBA.xlsm]2020 Q2 All'!R4C8:R60000C8,'[Inventory VBA.xlsm]2020 Q2 All'!R4C6:R60000C6,1,'[Inventory VBA.xlsm]2020 Q2 All'!R4C14:R60000C14,R4C14),2),""#,##0_ ;-#,##0 "")&""***"""
    Range("E2").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        Next ws
        
End Sub
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,087
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub PriorQuarterTotals()
   Dim ws As Worksheet
   For Each ws In ActiveWorkbook.Worksheets
      ws.Columns("Q:Q").Replace What:="#N/A", Replacement:="0", LookAt:=xlWhole, _
         SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
         ReplaceFormat:=False
   Next ws
   
   
   For Each ws In ActiveWorkbook.Worksheets
      With ws.Range("E2")
         .FormulaR1C1 = _
            "=""***Prior Quarter Retail Totals: $""&TEXT(ROUND(SUMIFS('[Inventory VBA.xlsm]2020 Q2 All'!R4C8:R60000C8,'[Inventory VBA.xlsm]2020 Q2 All'!R4C6:R60000C6,1,'[Inventory VBA.xlsm]2020 Q2 All'!R4C14:R60000C14,R4C14),2),""#,##0_ ;-#,##0 "")&""***"""
         .Value = .Value
         .Font.Bold = True
      End With
   Next ws
End Sub
 

Worker8ee

New Member
Joined
Aug 8, 2018
Messages
28
Yes that did the trick! Thank you Fluff!!! I will make sure to study this format, you have saved me!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,087
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,185
Messages
5,576,582
Members
412,735
Latest member
jwoods1204
Top