Dynamic AutoSum vba

Dave8899

New Member
Joined
Jan 17, 2019
Messages
30
I have written VBA that pulls data off Multiple sheets and adds it to an account page, what I need help with please is a VBA that auto sums column G & I up to first empty row above and this to loop, though this until all sections have auto sums in G and I under each different customer. Where it gets tricky is the number of rows each customer has is will change every time.
Excel.PNG


Sorry cant upload a mini book it a works PC and very restricted, as you can see a Customer can have different departments with different amounts per dept and a different number of rows etc, some have 3 or 4 some can have 50 +, it is dependent on the customer I want to have an auto sum for each department shown by the yellow cells (if I can get them to go yellow as well that would be a bonus) and there could be up to 20 customers on the report so this would be a massive time saver.

If it is possible an overall customer total (green) a few lines below the last entry (but this is not essential)
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Dave()
   Dim Rng As Range
   
   For Each Rng In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      With Rng.Offset(Rng.Count).Resize(1)
         .Offset(, 6).Formula = "=sum(" & Rng.Offset(, 6).Address(0, 0) & ")"
         .Offset(, 6).Interior.Color = vbYellow
         .Offset(, 8).Formula = "=sum(" & Rng.Offset(, 8).Address(0, 0) & ")"
         .Offset(, 8).Interior.Color = vbYellow
      End With
   Next Rng
End Sub
 
Solution

Dave8899

New Member
Joined
Jan 17, 2019
Messages
30
How about
VBA Code:
Sub Dave()
   Dim Rng As Range
  
   For Each Rng In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      With Rng.Offset(Rng.Count).Resize(1)
         .Offset(, 6).Formula = "=sum(" & Rng.Offset(, 6).Address(0, 0) & ")"
         .Offset(, 6).Interior.Color = vbYellow
         .Offset(, 8).Formula = "=sum(" & Rng.Offset(, 8).Address(0, 0) & ")"
         .Offset(, 8).Interior.Color = vbYellow
      End With
   Next Rng
End Sub
You are an absaluet star Thanks so much works perfectly
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,136,322
Messages
5,675,068
Members
419,549
Latest member
EliteBeat

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
Top