VBA that sums cells needs to loop and fill 11 more rows

John Sadler

New Member
Joined
May 1, 2012
Messages
48
Folks ... got this VBA to sum cells in specific sheets and put the values in the first row of a table in my Welcome sheet.

How do I get this to loop through the additional 11 rows in the sheets to fill the 11 rows in my table?

I have lined up the data somewhat as Sum of Q9 goes to Q9 in my Welcome sheet so Sum Q10 to Q10, Sum Q11 to Q11 etc

Sub SumIntPWSalesSheets()

' Sum PW Sales in Active Internal Sales Sheets

Application.ScreenUpdating = False

Dim ws As Worksheet
Dim Cell As Range
Dim Sum As Currency
Dim Sum2 As Currency

Sum = 0
Sum2 = 0

For Each ws In Worksheets

If ws.Range("U1") = "Active" And ws.Range("U2") = "Internal" Then

Sum = Sum + ws.Range("R9").Value
Sum2 = Sum2 + ws.Range("Q9").Value

End If

Next ws

Sheets("Welcome").Range("R9").Value = Sum
Sheets("Welcome").Range("Q9").Value = Sum2

Application.ScreenUpdating = True

End Sub
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,409
Office Version
365
Platform
Windows
Re: How do I get VBA that sums cells needs to loop and fill 11 more rows?

Hi, something like this maybe..

Code:
Sub SumIntPWSalesSheets()


' Sum PW Sales in Active Internal Sales Sheets


Application.ScreenUpdating = False


Dim ws As Worksheet
Dim Cell As Range
Dim Sum As Currency
Dim Sum2 As Currency
Dim iRow As Long


For iRow = 9 To 20
    
    Sum = 0
    Sum2 = 0


    For Each ws In Worksheets
        
        If ws.Range("U1") = "Active" And ws.Range("U2") = "Internal" Then
        
            Sum = Sum + ws.Range("R" & iRow).Value
            Sum2 = Sum2 + ws.Range("Q" & iRow).Value
        
        End If
    
    Next ws


    Sheets("Welcome").Range("R" & iRow).Value = Sum
    Sheets("Welcome").Range("Q" & iRow).Value = Sum2
    
Next i


Application.ScreenUpdating = True


End Sub
 

John Sadler

New Member
Joined
May 1, 2012
Messages
48
Re: How do I get VBA that sums cells needs to loop and fill 11 more rows?

Thanks will give that a try
cheers John
 

John Sadler

New Member
Joined
May 1, 2012
Messages
48
Re: How do I get VBA that sums cells needs to loop and fill 11 more rows?

Cool works. I had altered my code so had the row thing completely sorted but didn't think it was that easy to get it to loop through row 9-20. I had it so it would fill a selected row but you have given me exactly what i needed. Well done and thanks
Cheers John
 

Watch MrExcel Video

Forum statistics

Threads
1,101,996
Messages
5,484,089
Members
407,430
Latest member
sgoldman

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top