• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

oidamae

New Member
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!
 

Attachments

  • dummy.xlsx
    11.8 KB · Views: 2
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 :​
Code:
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 !
 
Yeah works well with this spreadsheet, lemme see if I can export it to the actual worksheet
 
Last edited by a moderator:
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 …​
 
Back
Top