Sep 07 2021 08:57 AM
Sep 07 2021 08:57 AM
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.
Sep 07 2021 11:11 AM
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.
Sep 07 2021 11:18 AM
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.
Sep 07 2021 12:16 PM
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.
Sep 07 2021 12:34 PMSolution
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.
Sep 07 2021 12:42 PM