GTretick
Member
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.
I've included the actual spreadsheet as well.
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
Last edited by a moderator: