Generating a repeating list of names from an ever changing roster.
smoore
is a relatively short list of constantly changing names. I need to put
this list of names in a repeating fashion in Sheet 1 Col B. Ideally I
would like for Sheet 1 to regenerate Col B each time the roster list is
changed. Is there a way to do this? Thanks.
Otto Moehrbach
involves VBA. Basically, Excel will recognize any change to the list of
names in Column A of sheet 2 and will execute whatever macro commands you
want when that happens. Post back with an explanation of "repeating
fashion". HTH Otto
"smoore" <smo...@ragsdalesteel.com> wrote in message
news:1146598563.6...@j73g2000cwa.googlegroups.com...
smoore
Let me explain a little better. Let's say that their are 12 names on
the roster, subject to change at any date. I need that list posted to
worksheet 1 Col B as is. Then right below it the same list again then
right below that the same thing again and so on for as many dates as I
have in worksheet 1 Col A. Then I would like to be able to alter the
roster as needed and have col B change with it. Thank you for taking a
look at this.
Otto Moehrbach
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 otto...@comcast.net. 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
"smoore" <smo...@ragsdalesteel.com> wrote in message
news:1146607570....@i39g2000cwa.googlegroups.com...
smoore
Thanks for a perfect solution. Greatly appreciated.
Scotty