VBA to copy a column to another column with the same date at the top

[armchairandy]

Hi

I have an input column with the date of input at the top and number values on each row of that column (30 no.). I then have a table of weekly dates horizontally in another section of the worksheet. What I need is to copy the data from the input column and paste the values into the corresponding date column in the historical table. Sort of creating a historical record of the values each week. After each week's input the data is copied to the corresponding date column in the historical table - they are both in the same workbook, but could be in separate workbooks in the future.


Input table

Col A
Heading:"Category"
Col B Heading "Input Date"
First Category in A3, the A4 and so on
The input Date is in B2
The value for the first category is in Col B3 against Category 1
Against each category there would be a numerical value under Column B

Historical Data Table

Columns AA2-to AZ2
Weekly dates

Under each corresponding date in the historical table would be that weeks values

[TABLE="width: 524"]
<colgroup><col><col><col span="2"><col><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 2, align: left"]Input table[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]Historical Table[/TD]
[/TR]
[TR]
[TD="align: right"] 24/05/19[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]03/05/19[/TD]
[TD="align: right"]10/05/19[/TD]
[TD="align: right"]17/05/19[/TD]
[TD="align: right"]24/05/19[/TD]
[/TR]
[TR]
[TD="align: left"]Category 1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Category 1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: left"]Category 2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Category 2[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: left"]Category 3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Category 3[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]9[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: left"]Category 4[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Category 4[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]Category 5[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Category 5[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD="align: left"]Category 6[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Category 6[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]Category 7[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Category 7[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD="align: left"]Category 8[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Category 8[/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD="align: left"]Category 9[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Category 9[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: left"]Category 10[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Category 10[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD="align: left"]Category 11[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Category 11[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]Category 12[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Category 12[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: left"]Category 13[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Category 13[/TD]
[TD] [/TD]
[TD]4[/TD]
[TD] [/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: left"]Category 14[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Category 14[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: left"]Category 15[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Category 15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]


The obvious solution would be to copy & paste by hand, but I would have up to 30 different input columns each week & a similar task will be carried out by many other people, so I need the procedure to be without effort. Any help would be appreciated. Hope this makes sense.

Andrew

 

[Repush]

try

Sub Macro1()
    Range("B2:B17").Copy
    WkOffset = (Range("B2").Value - Range("D2").Value) \ 7
    Range("D2").Offset(0, WkOffset).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

 

[armchairandy]

Repush

Many thanks for your help in this, sorry I didn't reply earlier, I was called away & had to put this on ice.

Regards

Andrew

 

[Repush]

no problem
glad I could help . .