Automatically move input data forward when spreadsheet is opened at start of each day

GTretick

Member
Joined
Jul 29, 2015
Messages
42
Reaction score
0
Points
6
Location
Canada
Excel Version(s)
365
I've created a spreadsheet that has user inputs for a grid that has days across the top and various items down the side.

At the start of each day I want to copy the data for day 2 to day n and paste it one day forward to day 1.

I've created a macro that basically does this.

My problem is getting the VBA to do this automatically when the spreadsheet is opened for the first time in the day.

My research shows Application.EnableEvents and worksheet_Change seem to be part of the solution but I can't figure out the syntax and logic behind adding them to the routine.

I've included the "incorrect" code I've tried. Can anybody modify what I have so that it works as I hope it would.

I'm new to VBA so any other advice you could give me in perhaps making my code more efficient would also be nice.

Thanks in advance.



Code:
Option Explicit
Private Sub worksheet_Change(ByVal Target As Date)
Dim firstday As Date
firstday = Range("h2").Value
If firstday <> Date Then
    Application.EnableEvents = False
        Range("I2:AB22").Select
        Selection.Copy
        Range("H2").Select
        ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
            IconFileName:=False
        Range("AB2:AB22").Select
        Range("AB22").Activate
        Selection.ClearContents
        Range("Z2:AA2").Select
        Selection.AutoFill Destination:=Range("Z2:AB2"), Type:=xlFillDefault
        Range("Z2:AB2").Select
    Application.EnableEvents = True
End If
End Sub


I've included the actual spreadsheet as well.
 

Attachments

  • MoveDates.xlsm
    17.3 KB · Views: 10
Last edited by a moderator:
.
Your macro : Private Sub worksheet_Change(ByVal Target As Date) is designed to run ONLY when a change is made to the worksheet.
The macro cannot be run when first opening your workbook, nor can you run it manually with a command button. Only by making a change to the
worksheet will the macro run.

I tried to manipulate your spreadsheet to get the macro to function but any entry I make to the worksheet causes an error. I am not entering the
correct data.

????
 
NoS - The change to "Range" seems to work. I didn't get to really test it today as the date has already changed but early indications seem to be good.

Thanks for your input everyone.
 
Back
Top