smoore
Here are a couple of macros to do what you want. These macros are
written with these conditions:
The first sheet is named "First".
The name of the second sheet doesn't matter.
The roster is in the second sheet in Column A starting in A2.
The dates are in the first sheet in Column A starting in A2.
The macros will copy the roster and paste it into Column B of the first
sheet starting with B2.
The macros will cut off the entries in Column B of the first sheet at the
last date entry in Column A.
Note that the first macro below is a Worksheet_Change macro and must be
placed in the sheet module of the second sheet. To access that module,
right-click on the sheet tab of the second sheet, select View Code, and
paste the macro into that module. Click on the "X" at the top right corner
of the module display to return to your spreadsheet.
The second macro below must be placed in a standard module.
In case you are unsure of where/how to place the macros, or word-wrap messes
up the code in this message, I am emailing you the small file I used for
this. I am sending this file to the email address that is attached to your
post. If this address is not valid, email me with a valid address and I'll
send you the file. My email address is (e-mail address removed). Remove the
"nop" from this address. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing _
And Target.Row > 1 Then _
Call UpdateData
End Sub
Sub UpdateData()
Dim Rng2ndNames As Range
Dim LastDate As Range
Set Rng2ndNames = Range("A2", _
Range("A" & Rows.Count).End(xlUp))
With Sheets("First")
If Not IsEmpty(.[B2].Value) Then .Range("B2", _
.Range("B" & Rows.Count).End(xlUp)).ClearContents
Do Until IsEmpty(.Range("B" &
Rows.Count).End(xlUp).Offset(, -1).Value)
Rng2ndNames.Copy .Range("B" & Rows.Count).End(xlUp).Offset(1)
Loop
Set LastDate = .Range("A" & Rows.Count).End(xlUp)
If Not IsEmpty(LastDate.Offset(1, 1).Value) Then _
.Range(LastDate.Offset(1, 1), _
.Range("B" & Rows.Count).End(xlUp)).ClearContents
End With
End Sub