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

[John Sadler]

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

 

[FormR]

Re: How do I get VBA that sums cells needs to loop and fill 11 more rows?

Hi, something like this maybe..

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]

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]

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