Adapting code to copy names on a roster

[ME1960]

I have used the pasted code for over 15 years to generate a daily sheet

****************************************
'* Daily Sheet Builder
'* BY M J EDGE
'* 16-12-2006
'* mods 1 - 3
'****************************************


Sub AnyDay()

Application.ScreenUpdating = False
Dim A$, b$, c$
Dim i
Dim Loop1 As Integer
Sheet12.Activate


For Loop1 = 1 To 7
Select Case Loop1
Case 1 'Monday
A$ = "C"
x = 3
Case 2 'Tuesday
A$ = "D"
x = 4
Case 3 'Wednesday
A$ = "E"
x = 5
Case 4 'Thursday
A$ = "F"
x = 6
Case 5 'Friday
A$ = "G"
x = 7
Case 6 'Saturday
A$ = "H"
x = 8
Case 7 'Sunday
A$ = "I"
x = 9
End Select
Sheet12.Activate
For i = 1 To 80
c$ = A$ & (i + 0) 'use the a$ Luke
Range(c$).Select
If ActiveCell.Value = "AD" Then
b$ = "A" & (i + 0)
Range(b$).Select
ActiveCell.Copy
Sheets(x).Activate
Range("F33").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End If
Next i

Sheet12.Activate
For i = 1 To 80
c$ = A$ & (i + 0) 'use the a$ Luke
Range(c$).Select
If ActiveCell.Value = "AD1" Then
b$ = "A" & (i + 0)
Range(b$).Select
ActiveCell.Copy
Sheets(x).Activate
Range("F34").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False[/CODE]
End If
Next i

This is an extract of the code that works perfectly if the roster is as follows

Landside Roster

Group 1	Man	Line	Mon	Tue	Wed	Thu	Fri	Sat	Sun
Driver	No	No	4-Oct	5-Oct	6-Oct	7-Oct	8-Oct	9-Oct	10-Oct
W Clarkson		1​	AD	AD2	AD3	AD	AD1
S Wild		2​		N1	N12	N8	N3
S Goddard		3​			M1	M17	M10	SM3
G Coughlan		4​				N1	N15	SN2	N3
L Swire		5​					AD4	AD3	AD2
J Whatling		6​	AD1					N1	N14
C Sinclair		7​	AD2	N3					M3
J Greenwood/E McDonagh		8​	M11	M4	SM3
M McLean		9​	N1	N13	SN2	N14
S Burrage		10​		M1	M14	M9	M17
N Bell		11​			N15	SN2	N10	N14
N Burke		12​				M3	M11	M8	M1
Y Haji/J Copeland		13​	M15				N1	N11	N8
C Lee		14​	N16					M10	M16
T McGovern		15​	M8	M3					N1
V DeLury		16​	N11	N8	N3

I have now changed company and the roster has changed again an extract

CD Roster10

x		Mon	Tue	Wed	Thu	Fri	Sat	Sun
	1​	AD	BD	CD	DD
	2​		AN	BN	CN	DN
Blue 10	3​			AD	BD	CD	DD
	4​				AN	BN	CN	DN
	5​					AD	BD	CD
	6​	DD					AN	BN
Orange 1	7​	CN	DN					AD
	8​	BD	CD	DD
	9​	AN	BN	CN	DN
	10​		AD	BD	CD	DD
Green 1	11​			AN	BN	CN	DN
	12​				AD	BD	CD	DD
	13​					AN	BN	CN
	14​	DN					AD	BD
Yellow 1	15​	CD	DD					AN
	16​	BN	CN	DN
	17​	AD1	BD1	CD1	DD1
	18​		AN1	BN1	CN1	DN1
Blue 1	19​			AD1	BD1	CD1	DD1
	20​				AN1	BN1	CN1	DN1
	21​					AD1	BD1	CD1
	22​	DD1					AN1	BN1
Orange 2	23​	CN1	DN1					AD1
	24​	BD1	CD1	DD1
	25​	AN1	BN1	CN1	DN1
	26​		AD1	BD1	CD1	DD1
Green 2	27​			AN1	BN1	CN1	DN1
	28​				AD1	BD1	CD1	DD1
	29​					AN1	BN1	CN1
	30​	DN1					AD1	BD1
Yellow 2	31​	CD1	DD1					AN1
	32​	BN1	CN1	DN1
	33​	AD2	BD2	CD2	DD2
	34​		AN2	BN2	CN2	DN2
Blue 2	35​			AD2	BD2	CD2	DD2
	36​				AN2	BN2	CN2	DN2
	37​					AD2	BD2	CD2
	38​	DD2					AN2	BN2
Orange 3	39​	CN2	DN2					AD2
	40​	BD2	CD2	DD2
	41​	AN2	BN2	CN2	DN2
	42​		AD2	BD2	CD2	DD2
Green3	43​			AN2	BN2	CN2	DN2
	44​				AD2	BD2	CD2	DD2
	45​					AN2	BN2	CN2
	46​	DN2					AD2	BD2
Yellow 3	47​	CD2	DD2					AN2

what I need to change is if it finds say AD on Monday and its a blank in column A skip to the next instance of AD on Monday until it finds a name in column A and copy that to the daily sheet. I am sure that my current code can be adapted but just seem to have forgot so much in the intervening years.

Thanks in advance