VBA copy formula in cell across a row (multiple times for different rows)

jtkerlin

New Member
Joined
Jul 15, 2019
Messages
1
Brand new to vba, I did some searching around, but did not find what I was looking for or did not understand how I would leverage. Likely there is a better way to what I am trying to do.

I have a data table (originally from a pivot table) and added sums for various rows - as users will be able to update the values being summed (I want the totals to update with the new entries), I would like to copy those formulas across multiple columns to the right.

Here is an example of the data I have set up. Users could change the amount for Prod1 for January, or February or March and so forth.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Client
[/TD]
[TD]Product
[/TD]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[TD]Mar
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]ABC
[/TD]
[TD]Prod1
[/TD]
[TD]10
[/TD]
[TD]10
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD]Prod2
[/TD]
[TD]15
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD]Prod Total
[/TD]
[TD]25
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]ABC Total
[/TD]
[TD][/TD]
[TD]25
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]XYZ
[/TD]
[TD]Prod2
[/TD]
[TD]15
[/TD]
[TD]5
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD]Prod4
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD]Prod8
[/TD]
[TD]1
[/TD]
[TD]7
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD]Prod Total
[/TD]
[TD]16
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]XYZ Total
[/TD]
[TD][/TD]
[TD]16
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 10"]
<tbody>[TR]

[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
The only determination of which row to have the cell value (formula) copied over is that in Column A and Column B it ends with " Total". Aside from that there could be a variable number of Products for each Client, it could be one, two, twenty, etc. The number of clients I have is well in excess of 1,000 (this is just a snippet).

I would want it to take the formula from C4 and copy to the end of the row (would be to December - not shown), then it would take the formula from C5 and copy to the end of the row, then C9...., then C10, until it reaches the end of the data filed.

Thank you for any help and understanding on this. I know I would need to do some sort of loop to have it repeat, but I cannot figure out how it would identify the rows where I would want the cell value (formula) copied over.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Threads
1,223,907
Messages
6,175,300
Members
452,633
Latest member
DougMo

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