Hi,I am new to this forum (and new to VBA!)
Here is my issue (which I'm sure will be simple on here!)
I have a table with both period nos (ie 1-13) and weeks nos (1-52) across the top.
I have a formula that copies data from from one file to another into each cell of a column of 20 items for that particular period/week (simple offset formula).
However what I want to do is overwrite (using vba) and paste special the previous "weeks" cells to ensure that the formulas in the preceding cells are in tact.
So sample below of table..
[TABLE="width: 618"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 12"][/TD]
[/TR]
[TR]
[TD] Pd/Week No[/TD]
[TD="colspan: 4"]1[/TD]
[TD="colspan: 4"]2[/TD]
[TD="colspan: 4"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Actual[/TD]
[TD]6.5[/TD]
[TD]6.5[/TD]
[TD]6.5[/TD]
[TD]6.5[/TD]
[TD]7.3[/TD]
[TD]7.3[/TD]
[TD]7.3[/TD]
[TD]7.3[/TD]
[TD]3.3[/TD]
[TD]3.3[/TD]
[TD]3.3[/TD]
[TD]3.3[/TD]
[/TR]
[TR]
[TD]Target[/TD]
[TD]6.0[/TD]
[TD]6.0[/TD]
[TD]6.0[/TD]
[TD]6.0[/TD]
[TD]6.0[/TD]
[TD]6.0[/TD]
[TD]6.0[/TD]
[TD]6.0[/TD]
[TD]5.8[/TD]
[TD]5.8[/TD]
[TD]5.8[/TD]
[TD]5.8[/TD]
[/TR]
[TR]
[TD]Actual[/TD]
[TD]4.0[/TD]
[TD]4.0[/TD]
[TD]4.0[/TD]
[TD]4.0[/TD]
[TD]14.0[/TD]
[TD]14.0[/TD]
[TD]14.0[/TD]
[TD]14.0[/TD]
[TD]10.3[/TD]
[TD]10.3[/TD]
[TD]10.3[/TD]
[TD]10.3[/TD]
[/TR]
[TR]
[TD]Target[/TD]
[TD]8.0[/TD]
[TD]8.0[/TD]
[TD]8.0[/TD]
[TD]8.0[/TD]
[TD]6.8[/TD]
[TD]6.8[/TD]
[TD]6.8[/TD]
[TD]6.8[/TD]
[TD]7.5[/TD]
[TD]7.5[/TD]
[TD]7.5[/TD]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]Actual[/TD]
[TD]41.5[/TD]
[TD]41.5[/TD]
[TD]41.5[/TD]
[TD]41.5[/TD]
[TD]36.3[/TD]
[TD]36.3[/TD]
[TD]36.3[/TD]
[TD]36.3[/TD]
[TD]30.0[/TD]
[TD]30.0[/TD]
[TD]30.0[/TD]
[TD]30.0[/TD]
[/TR]
[TR]
[TD]Target[/TD]
[TD]33.5[/TD]
[TD]33.5[/TD]
[TD]33.5[/TD]
[TD]33.5[/TD]
[TD]31.0[/TD]
[TD]31.0[/TD]
[TD]31.0[/TD]
[TD]31.0[/TD]
[TD]33.8[/TD]
[TD]33.8[/TD]
[TD]33.8[/TD]
[TD]33.8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Any thoughts?
I am using Excel 2010
Here is my issue (which I'm sure will be simple on here!)
I have a table with both period nos (ie 1-13) and weeks nos (1-52) across the top.
I have a formula that copies data from from one file to another into each cell of a column of 20 items for that particular period/week (simple offset formula).
However what I want to do is overwrite (using vba) and paste special the previous "weeks" cells to ensure that the formulas in the preceding cells are in tact.
So sample below of table..
[TABLE="width: 618"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 12"][/TD]
[/TR]
[TR]
[TD] Pd/Week No[/TD]
[TD="colspan: 4"]1[/TD]
[TD="colspan: 4"]2[/TD]
[TD="colspan: 4"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Actual[/TD]
[TD]6.5[/TD]
[TD]6.5[/TD]
[TD]6.5[/TD]
[TD]6.5[/TD]
[TD]7.3[/TD]
[TD]7.3[/TD]
[TD]7.3[/TD]
[TD]7.3[/TD]
[TD]3.3[/TD]
[TD]3.3[/TD]
[TD]3.3[/TD]
[TD]3.3[/TD]
[/TR]
[TR]
[TD]Target[/TD]
[TD]6.0[/TD]
[TD]6.0[/TD]
[TD]6.0[/TD]
[TD]6.0[/TD]
[TD]6.0[/TD]
[TD]6.0[/TD]
[TD]6.0[/TD]
[TD]6.0[/TD]
[TD]5.8[/TD]
[TD]5.8[/TD]
[TD]5.8[/TD]
[TD]5.8[/TD]
[/TR]
[TR]
[TD]Actual[/TD]
[TD]4.0[/TD]
[TD]4.0[/TD]
[TD]4.0[/TD]
[TD]4.0[/TD]
[TD]14.0[/TD]
[TD]14.0[/TD]
[TD]14.0[/TD]
[TD]14.0[/TD]
[TD]10.3[/TD]
[TD]10.3[/TD]
[TD]10.3[/TD]
[TD]10.3[/TD]
[/TR]
[TR]
[TD]Target[/TD]
[TD]8.0[/TD]
[TD]8.0[/TD]
[TD]8.0[/TD]
[TD]8.0[/TD]
[TD]6.8[/TD]
[TD]6.8[/TD]
[TD]6.8[/TD]
[TD]6.8[/TD]
[TD]7.5[/TD]
[TD]7.5[/TD]
[TD]7.5[/TD]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]Actual[/TD]
[TD]41.5[/TD]
[TD]41.5[/TD]
[TD]41.5[/TD]
[TD]41.5[/TD]
[TD]36.3[/TD]
[TD]36.3[/TD]
[TD]36.3[/TD]
[TD]36.3[/TD]
[TD]30.0[/TD]
[TD]30.0[/TD]
[TD]30.0[/TD]
[TD]30.0[/TD]
[/TR]
[TR]
[TD]Target[/TD]
[TD]33.5[/TD]
[TD]33.5[/TD]
[TD]33.5[/TD]
[TD]33.5[/TD]
[TD]31.0[/TD]
[TD]31.0[/TD]
[TD]31.0[/TD]
[TD]31.0[/TD]
[TD]33.8[/TD]
[TD]33.8[/TD]
[TD]33.8[/TD]
[TD]33.8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Any thoughts?
I am using Excel 2010