Move cell info on new entry

B

belvy123

Hi All

I am wanting to be able to enter dates into a cell in my sheet and each time
I enter a new date it will automatically move the last date down a row. I
want to have the most current 4 dates show. So there will be 4 rows of dates.

example

3/25/07
3/15/07
3/10/07
3/3/07

and now when I enter a new date it will look like this

3/30/07
3/25/07
3/15/07
3/10/07


Has anyone ever done this or know how to go about writiung a function or
formula to do this

Thanks for the help

DN
 
B

Bob Phillips

Where are the existing dates? Where will the new date go?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
B

belvy123

Hi Bob

The date on top will be the most current date and I feel it would be best if
I entered the new date into a empty cell. so I would want that cell to clear
after it was entered and carry that new date to the top of the list below and
remove the date from the bottom of the list so it updated each time a new
date was entered.

I hope that may clear it up some
Thanks

DN
 
B

belvy123

Hi AGain Bob



A
1 (blank to enter new date)
2 3/25/07
3 3/15/07
4 3/10/07


After new date is entered it will look like this

A
1 (Blank to enter new date)
2 3/30/07
3 3/25/07
4 3/15/07

Hopefully that is a bit easier to understand
Thanks
DN
 
B

Bob Phillips

Perfect mate!

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Resize(3).Copy .Offset(1, 0)
.Value = ""
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
B

belvy123

Hi Agian Bob

What would I need to add to this script to be able to do this in several
locations on the same sheet.

example

in A1 B1 C1 D1 E1

Etc Etc
 
B

Bob Phillips

Change

Const WS_RANGE As String = "A1" '<== change to suit

to

Const WS_RANGE As String = "A1:E1"

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
B

belvy123

Thanks again Bob
--
cruchnin numbers


Bob Phillips said:
Change

Const WS_RANGE As String = "A1" '<== change to suit

to

Const WS_RANGE As String = "A1:E1"

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...
 
Top