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,422
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,081
Messages
5,509,150
Members
408,711
Latest member
EMexcel

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top