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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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