Shift cells to the left
 

Hi all. Below I have the following formulas in cells B3-F3. What I am trying
to do, is any data that I have in cell B4-F9, I would like that data to shift
to the left when the date changes in cell B3-F3.

B C D E F
3 =NOW() =NOW()+1 =NOW()+2 =NOW()+3 =NOW()+4
4
5
6
7
8
9

Thanks in advance for any help you might have.
Steve

Shift cells to the left
 

This will work, but you need to be aware of
the quirks.
1. For Cells(1, 11) reference, you will
need to pick a cell on your worksheet, that you
know will not be used for anything else and
is not on the rows that will shift, to
substitute that cell reference

2. The data that goes into that cell is set
each time the macro runs, but will not change
until the date changes. The date changes by
your computer date change with the NOW() function.

3. When the cells shift left, the extreme right
column for the shifted rows that previously had
data becomes blank, leaving only the date header.

If you can live with those things then copy this
to your worksheet code module for the sheet you
want to change.

Private Sub Worksheet_Activate()
If Worksheets(1).Cells(3, 2) < Worksheets(1).Cells(1, 11) Then
Worksheets(1).Range("B4:B9").Delete xlShiftToLeft
End If
x = Format(Cells(1, 1).Value, "m/d/yy")
Worksheets(1).Range("K1") = x
End Sub



"Steve D." wrote:

Hi all. Below I have the following formulas in cells B3-F3. What I am trying
to do, is any data that I have in cell B4-F9, I would like that data to shift
to the left when the date changes in cell B3-F3.

B C D E F
3 =NOW() =NOW()+1 =NOW()+2 =NOW()+3 =NOW()+4
4
5
6
7
8
9

Thanks in advance for any help you might have.
Steve

Shift cells to the left
 

Corrects the value for x to "B3" date ref:

Private Sub Worksheet_Activate()
If Worksheets(1).Cells(3, 2) < Worksheets(1).Cells(1, 11) Then
Worksheets(1).Range("B4:B9").Delete xlShiftToLeft
End If
x = Format(Cells(3, 2).Value, "m/d/yy")
Worksheets(1).Range("K1") = x
End Sub


"Steve D." wrote:

Hi all. Below I have the following formulas in cells B3-F3. What I am trying
to do, is any data that I have in cell B4-F9, I would like that data to shift
to the left when the date changes in cell B3-F3.

B C D E F
3 =NOW() =NOW()+1 =NOW()+2 =NOW()+3 =NOW()+4
4
5
6
7
8
9

Thanks in advance for any help you might have.
Steve

Shift cells to the left
 

Steve, sorry to keep changing this on you but you
do want something that works right, so this will do
the trick if you change the =Now() in "B3" to =TODAY().
The date in the trigger cell kept mismatching due to the
time reference. This tested OK and will only shift the cells
on the first activation on the same day.

Private Sub Worksheet_Activate() 'Shift range of cells on date change
If Worksheets(1).Cells(3, 2) < Worksheets(1).Cells(1, 11) Then
Worksheets(1).Range("B4:B9").Delete xlShiftToLeft
End If
x = Worksheets(1).Cells(3, 2).Value
Worksheets(1).Range("K1") = Format(x, "m/d/yy")
End Sub

If for some reason, it does continue to shift on each iteration
of the code on the same day, then the date mismatch is the
problem and you can play with the formatting until it works or
somebody gives you a better code set.

"Steve D." wrote:

Hi all. Below I have the following formulas in cells B3-F3. What I am trying
to do, is any data that I have in cell B4-F9, I would like that data to shift
to the left when the date changes in cell B3-F3.

B C D E F
3 =NOW() =NOW()+1 =NOW()+2 =NOW()+3 =NOW()+4
4
5
6
7
8
9

Thanks in advance for any help you might have.
Steve

Shift cells to the left
 

JLGWhiz,
Thanks for the quick response and answer to my question, works great.
Steve

"JLGWhiz" wrote:

Steve, sorry to keep changing this on you but you
do want something that works right, so this will do
the trick if you change the =Now() in "B3" to =TODAY().
The date in the trigger cell kept mismatching due to the
time reference. This tested OK and will only shift the cells
on the first activation on the same day.

Private Sub Worksheet_Activate() 'Shift range of cells on date change
If Worksheets(1).Cells(3, 2) < Worksheets(1).Cells(1, 11) Then
Worksheets(1).Range("B4:B9").Delete xlShiftToLeft
End If
x = Worksheets(1).Cells(3, 2).Value
Worksheets(1).Range("K1") = Format(x, "m/d/yy")
End Sub

If for some reason, it does continue to shift on each iteration
of the code on the same day, then the date mismatch is the
problem and you can play with the formatting until it works or
somebody gives you a better code set.

"Steve D." wrote:

Hi all. Below I have the following formulas in cells B3-F3. What I am trying
to do, is any data that I have in cell B4-F9, I would like that data to shift
to the left when the date changes in cell B3-F3.

B C D E F
3 =NOW() =NOW()+1 =NOW()+2 =NOW()+3 =NOW()+4
4
5
6
7
8
9

Thanks in advance for any help you might have.
Steve