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

armchairandy

Board Regular
Joined
Mar 27, 2012
Messages
51
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

Input tableHistorical Table
24/05/19 03/05/1910/05/1917/05/1924/05/19
Category 11Category 11 1
Category 21Category 24 21
Category 31Category 3 191
Category 4 Category 4647
Category 54Category 53 4
Category 6 Category 6463
Category 75Category 71355
Category 84Category 87414
Category 92Category 901 2
Category 103Category 100713
Category 11 Category 11401
Category 121Category 121011
Category 131Category 13 4 1
Category 142Category 14 1 2
Category 159Category 15 9

<colgroup><col><col><col span="2"><col><col span="2"><col span="2"></colgroup><tbody>
</tbody>


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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Repush

Board Regular
Joined
Sep 21, 2015
Messages
133
try
Code:
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

Board Regular
Joined
Mar 27, 2012
Messages
51
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,909
Messages
5,483,660
Members
407,399
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top