Move columns to the left every new day

[04herrs]

I have dates that will move to the left every day. There is data that pertains to those dates. How do I get the data to move with the dates?

thank you

 

[NoSparks]

Try this on the workbook you linked to.

On the Weekly Task Schedule sheet:
Put formula =Today() in cell Z1.
Enter a whole bunch of data into your calendar so you can easily see if it moves.
Right click on the sheet tab.
Select View Code.
Paste this into the sheet module that opens.

Private Sub Worksheet_Calculate()
' check if first day is changed
If [b8] = [z1] Then Exit Sub

  Dim a, i As Long
  
On Error GoTo CleanExit:
Application.ScreenUpdating = False
Application.EnableEvents = False

' days since last data shift
i = [b8] - [z1]

If i > 0 And i < 7 Then
    'store data to shift
    a = Range(Cells(10, 2 + (i * 2)), Cells(40, 15)).Value
    'clear old data
    Range("b10:o40").ClearContents
    'write stored data
    Cells(10, 2).Resize(31, 14 - (i * 2)) = a
Else
    'clear old data
    Range("b10:o40").ClearContents
End If
    
' store new date
Range("z1") = Range("b8").Value

Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub

CleanExit:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Err.Clear
  
End Sub

Test a change of day by adding +1 to the existing formula that sets the StartDate in O6.
There is no provision for the days going "backwards", the StartDate cannot be prior to today.

 

[04herrs]

Try this on the workbook you linked to.

On the Weekly Task Schedule sheet:
Put formula =Today() in cell Z1.
Enter a whole bunch of data into your calendar so you can easily see if it moves.
Right click on the sheet tab.
Select View Code.
Paste this into the sheet module that opens.

Private Sub Worksheet_Calculate()
' check if first day is changed
If [b8] = [z1] Then Exit Sub

  Dim a, i As Long
  
On Error GoTo CleanExit:
Application.ScreenUpdating = False
Application.EnableEvents = False

' days since last data shift
i = [b8] - [z1]

If i > 0 And i < 7 Then
    'store data to shift
    a = Range(Cells(10, 2 + (i * 2)), Cells(40, 15)).Value
    'clear old data
    Range("b10:o40").ClearContents
    'write stored data
    Cells(10, 2).Resize(31, 14 - (i * 2)) = a
Else
    'clear old data
    Range("b10:o40").ClearContents
End If
    
' store new date
Range("z1") = Range("b8").Value

Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub

CleanExit:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Err.Clear
  
End Sub

Test a change of day by adding +1 to the existing formula that sets the StartDate in O6.
There is no provision for the days going "backwards", the StartDate cannot be prior to today.

this is awesome!! Thank you. It works!! Now I just have to figure out the other stuff like highlighting cells, tracking changes by user name and data on what events get used.

Again thank you!!!