Possible to copy data from one cell to another when today = certain date/time?

[neil4444]

Hi everybody, first time discussing here.

I'm working with Office 365, Win10. I'm not great at formulas. My team has a few different bundles of equipment that move from location-to-location every couple of weeks, so I'm using an Excel sheet to track where the equipment currently is and where it has been scheduled to go next. Currently, we are updating it manually.

Is it possible to create a formula that: when the current date is the same as Column I, the file can copy data from Column I>G... & Column H>F? I'd like to just be able to manually enter the "Upcoming" columns, and then it automatically copies once the day arrives, maybe aftera Data Refresh. I'm not sure if this is possible.

I have tried an IF formula that checks the DATEVALUE or TIMEVALUE, but I have not been able to make it copy, and I'm not sure if I need to change the date columns' date formatting.

Thank you!!

[#2]

@neil4444 

It would be a LOT easier to help if, instead of an image, you could post a copy of the actual spreadsheet. Just make sure it contains no proprietary or confidential information.

[#3]

@neil4444 

You cannot do this with formulas, it requires VBA code.

Press Alt+F11 to activate the visual Basic Editor.

Double-click ThisWorkbook in the Project Explorer pane on the left, under Microsoft Excel Objects.

Copy the following code into the code window:

Private Sub Workbook_Open()
    Dim wsh As Worksheet
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    Set wsh = ActiveSheet ' or Worksheets("Planning") or similar
    m = wsh.Range("I" & wsh.Rows.Count).End(xlUp).Row
    For r = 6 To m
        If wsh.Range("I" & r).Value <> "" And wsh.Range("I" & r).Value < Date Then
            wsh.Range("F" & r).Resize(, 2).Value = wsh.Range("H" & r).Resize(, 2).Value
            wsh.Range("H" & r).Resize(, 2).ClearContents
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open it.

Each time the workbook is opened, the code will automatically check he dates in column I and move the building and date to the left if necessary.

[#4]

@Hans Vogelaar

Sorry @mathetes, thank you! I added an example sheet and just removed the data, but all cells are in the same rows/columns that they would be on the real thing.

@Hans Vogelaar Thanks for letting me know formulas won't work. I have very intro-level experience with Visual Basic, 10 years ago.

It's actually 2 different sheets in the workbook I'm looking to apply this to. I copied your code to the first one, and saved as .xlsm. I wasn't able to get it to work though. If you see on the attachment how the headers are not in the header row and there is blank space in between, I think that would affect the code, no? It's not organized like a typical data table, it was organized for visual clarity, which might get in the way of the code, I would think.

[#5 · Accepted solution]

@neil4444 

The code should go into the ThisWorkbook module, not into the worksheet modules.

And it does need modification to work in two sheets and with merged cells.

See the attached version - the cells should be moved when you open it and allow macros.

[#6]

Wow, thank you so much, this is really awesome!!!!

I'm going to have to educate myself a bit, using VBA wouldn't have occured to me. Can't thank you enough for the help!!!!