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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,030
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,030
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,058
Messages
5,575,850
Members
412,688
Latest member
KYLE1788
Top