VBA to copy a column to another column with the same date at the top
Results 1 to 4 of 4

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

  1. #1
    New Member
    Join Date
    Mar 2012
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 table Historical Table
    24/05/19 03/05/19 10/05/19 17/05/19 24/05/19
    Category 1 1 Category 1 1 1
    Category 2 1 Category 2 4 2 1
    Category 3 1 Category 3 1 9 1
    Category 4 Category 4 6 4 7
    Category 5 4 Category 5 3 4
    Category 6 Category 6 4 6 3
    Category 7 5 Category 7 1 3 5 5
    Category 8 4 Category 8 7 4 1 4
    Category 9 2 Category 9 0 1 2
    Category 10 3 Category 10 0 7 1 3
    Category 11 Category 11 4 0 1
    Category 12 1 Category 12 1 0 1 1
    Category 13 1 Category 13 4 1
    Category 14 2 Category 14 1 2
    Category 15 9 Category 15 9


    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

  2. #2
    Board Regular
    Join Date
    Sep 2015
    Location
    the Netherlands
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  3. #3
    New Member
    Join Date
    Mar 2012
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  4. #4
    Board Regular
    Join Date
    Sep 2015
    Location
    the Netherlands
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    no problem
    glad I could help . .

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •