VBA for Subtotal and Copy/Paste with variable ranges

CPA2422

New Member
Joined
Jul 16, 2018
Messages
1
I'm trying to figure out excel VBA code to add subtotal (=SUBTOTAL(9,__:_) formulas to multiple Total Rows within a worksheet (red boxed. The issue is that the range for the subtotals can be any height. Per the image below, the hope is that the VBA code would be able to identify when "Total" is present in column B and then proceed to add a subtotal formula in that row for columns E to J. The subtotal formula would need to pull the above balances up until there is a blank cell in order to calculate the amount for the particular client (the length of the range is signified by the arrows in the image below). The range could be any number of rows so has to be fully variable.
In addition, trying to add a subtotal at the very bottom of the worksheet (last row +2) that should extend up to the very top of the row (blue arrow).
Finally, I need to find a way to copy the client name (green rectangles in column B) next to the total cells (copy to column C). The number of invoices can vary for each client so it would have to identify where the client name is located and then copy the name down to the bottom where the client total is being calculated.
Any help in this would be greatly appreciated!!



tempsnip.png
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

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