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.
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.