VBA that sums cells needs to loop and fill 11 more rows
Results 1 to 4 of 4

Thread: VBA that sums cells needs to loop and fill 11 more rows
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2012
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,056
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default 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
    [code]your code[/code]

  3. #3
    New Member
    Join Date
    May 2012
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  4. #4
    New Member
    Join Date
    May 2012
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •