HELP! How DO I make a macro to to copy/paste values based on changing column date

[oidamae]

Hi guys
So at my job, I have to copy and paste data from one sheet to another to get daily changes. I came up with a dummy workbook to try come up with a macro to help the process. I have attached the workbook. Ideally, in sheet 2, I need to copy the formula for range S2:S8 to column T. Then I select columns R & S and paste them to sheet 1 as values on column N and O. Then I need to change the formula on B12 to show that my dates have changed. This process repeats every day. I was wondering if there was a code that shows excel which columns to pick based on the new column. Please help!

 

[Marc L]

Hi, according to the Macro Recorder - a must use ! - and according to VBA help - a must read, all is there ! - a starter VBA demonstration​

you have to fit for what you forgot / misexplained :​

Sub Demo1()
    With Sheet2.[A2].CurrentRegion.Columns
         Sheet1.[N2:O8] = .Item(.Count - 1).Resize(, 2).Value
        .Parent.Range("B12:B17").Formula = "=" & .Cells(2, .Count).Address(False, False) & "/" _
                                               & .Cells(2, .Count - 1).Address(False, False) & "-1"
        .Item(.Count).AutoFill .Item(.Count).Resize(, 2)
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[oidamae]

Yeah works well with this spreadsheet, lemme see if I can export it to the actual worksheet

 

[Marc L]

An attachment not well reflecting the real workbook has no sense​

except if you are enough confident with your Excel / VBA skills to well fit it …​

​