Forum Discussion
Possible to copy data from one cell to another when today = certain date/time?
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.
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 SubSwitch 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.
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.
HansVogelaar 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.
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.
- 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!!!!