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
I have now changed company and the roster has changed again an extract
CD Roster10
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
****************************************
'* 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