Copying one cell to muliple cells

[GW Dyson]

Cell a1 is a date cell. Cells b1-b30 are date cells. What I am trying to do
is every time that I enter a new date in Ai, I want it to go to a blank cell
in b column and not over right the dates that are already there.

A B
2/3/04 1/6/04
1/20/04
1/25/04
2/3/04

 

[Gord Dibben]

GW

This would involve Event code behind your worksheet.

Right-click on your sheet tab and "View Code"

Copy/paste the code below into that sheet module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$A$1" And Target.Value <> "" Then
ActiveSheet.Cells(Rows.Count, 2).End(xlUp) _
.Offset(1, 0).Value = Target.Value
End If
stoppit:
Application.EnableEvents = True
End Sub

When you enter the first date in A1, B2 will receive that date. Subsequent
date entries in A1 will be sent to next available row in Column B.

You can select B1 and Edit>Delete>Shift Cells Up after first entry in A1.

Gord Dibben Excel MVP