Create Excel Weekly Table

[healey21]

I have been asked to create a weekly table via VBA (sadly my knowledge isn't this good here), the concept is to indicate Date's and below Week Day (Like Monday, Tuesday etc), then list peoples names and Position. Then repeat with as a new table for the next 6 months, with a blank row between each week.

But then in the table a person has to be designated on Primary or Secondary and this rotates each week. Each week the Primary will move down a week and rotate so I have indicated them as PS1 or PS2 or PS3, then the Secondary will be also rotated each week so I have indicated them as DC1 or DC2 or DC3 or DC4 or DC5. An example is shown below:

S Rota


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 82px"><COL style="WIDTH: 64px"><COL style="WIDTH: 75px"><COL style="WIDTH: 75px"><COL style="WIDTH: 75px"><COL style="WIDTH: 75px"><COL style="WIDTH: 76px"><COL style="WIDTH: 75px"><COL style="WIDTH: 75px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #008080">Primary</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BACKGROUND-COLOR: #ffff00">Secondary</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">01/01/2011</TD><TD style="TEXT-ALIGN: right">02/01/2011</TD><TD style="TEXT-ALIGN: right">03/01/2011</TD><TD style="TEXT-ALIGN: right">04/01/2011</TD><TD style="TEXT-ALIGN: right">05/01/2011</TD><TD style="TEXT-ALIGN: right">06/01/2011</TD><TD style="TEXT-ALIGN: right">07/01/2011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">Saturday</TD><TD style="TEXT-ALIGN: right">Sunday</TD><TD style="TEXT-ALIGN: right">Monday</TD><TD style="TEXT-ALIGN: right">Tuesday</TD><TD style="TEXT-ALIGN: right">Wednesday</TD><TD style="TEXT-ALIGN: right">Thursday</TD><TD style="TEXT-ALIGN: right">Friday</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Bill Jones</TD><TD>PS1</TD><TD style="BACKGROUND-COLOR: #ffff00">Secondary</TD><TD style="BACKGROUND-COLOR: #ffff00">Secondary</TD><TD style="BACKGROUND-COLOR: #ffff00">Secondary</TD><TD style="BACKGROUND-COLOR: #ffff00">Secondary</TD><TD style="BACKGROUND-COLOR: #ffff00">Secondary</TD><TD style="BACKGROUND-COLOR: #ffff00">Secondary</TD><TD style="BACKGROUND-COLOR: #ffff00">Secondary</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Sarah Brown</TD><TD>PS2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Allan Ball</TD><TD>PS3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Keith Blake</TD><TD>DC1</TD><TD style="BACKGROUND-COLOR: #008080">Primary</TD><TD style="BACKGROUND-COLOR: #008080">Primary</TD><TD style="BACKGROUND-COLOR: #008080">Primary</TD><TD style="BACKGROUND-COLOR: #008080">Primary</TD><TD style="BACKGROUND-COLOR: #008080">Primary</TD><TD style="BACKGROUND-COLOR: #008080">Primary</TD><TD style="BACKGROUND-COLOR: #008080">Primary</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>Jane Wills</TD><TD>DC2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>David White</TD><TD>DC3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>Tom Jones</TD><TD>DC4</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>Toya Watson</TD><TD>DC5</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">08/01/2011</TD><TD style="TEXT-ALIGN: right">09/01/2011</TD><TD style="TEXT-ALIGN: right">10/01/2011</TD><TD style="TEXT-ALIGN: right">11/01/2011</TD><TD style="TEXT-ALIGN: right">12/01/2011</TD><TD style="TEXT-ALIGN: right">13/01/2011</TD><TD style="TEXT-ALIGN: right">14/01/2011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">Saturday</TD><TD style="TEXT-ALIGN: right">Sunday</TD><TD style="TEXT-ALIGN: right">Monday</TD><TD style="TEXT-ALIGN: right">Tuesday</TD><TD style="TEXT-ALIGN: right">Wednesday</TD><TD style="TEXT-ALIGN: right">Thursday</TD><TD style="TEXT-ALIGN: right">Friday</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD>Bill Jones</TD><TD>PS1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD>Sarah Brown</TD><TD>PS2</TD><TD style="BACKGROUND-COLOR: #ffff00">Secondary</TD><TD style="BACKGROUND-COLOR: #ffff00">Secondary</TD><TD style="BACKGROUND-COLOR: #ffff00">Secondary</TD><TD style="BACKGROUND-COLOR: #ffff00">Secondary</TD><TD style="BACKGROUND-COLOR: #ffff00">Secondary</TD><TD style="BACKGROUND-COLOR: #ffff00">Secondary</TD><TD style="BACKGROUND-COLOR: #ffff00">Secondary</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD>Allan Ball</TD><TD>PS3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD>Keith Blake</TD><TD>DC1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD>Jane Wills</TD><TD>DC2</TD><TD style="BACKGROUND-COLOR: #008080">Primary</TD><TD style="BACKGROUND-COLOR: #008080">Primary</TD><TD style="BACKGROUND-COLOR: #008080">Primary</TD><TD style="BACKGROUND-COLOR: #008080">Primary</TD><TD style="BACKGROUND-COLOR: #008080">Primary</TD><TD style="BACKGROUND-COLOR: #008080">Primary</TD><TD style="BACKGROUND-COLOR: #008080">Primary</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD>David White</TD><TD>DC3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD>Tom Jones</TD><TD>DC4</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD>Toya Watson</TD><TD>DC5</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>



Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[Trevor G]

Hi Healey21

I am looking at your Thread and have done a basic create the first table via VBA, it is very long winded but it will create it for you.

The next stage will to be working on the second table etc.

Here is the code I have created.

Sub FillTables()
Dim strName1 As String, strName2 As String, strName3 As String, strName4 As String
Dim strName5 As String, strName6 As String, strName7 As String, strName8 As String
Dim strPS1 As String, strPS2 As String, strPS3 As String
Dim strDC1 As String, strDC2 As String, strDC3 As String, strDC4 As String, strDC5 As String
strName1 = "Bill Jones"
strName2 = "Sarah Brown"
strName3 = "Allan Ball"
strName4 = "Keith Blake"
strName5 = "Jane Wills"
strName6 = "David White"
strName7 = "Tom Jones"
strName8 = "Toya Watson"
strPS1 = "PS1"
strPS2 = "PS2"
strPS3 = "PS3"
strDC1 = "DC1"
strDC2 = "DC2"
strDC3 = "DC3"
strDC4 = "DC4"
strDC5 = "DC5"
Range("A1") = "Primary"
Range("A1").Interior.Color = 5287936
Range("A2") = "Secondary"
Range("A2").Interior.Color = vbYellow
Range("A5") = strName1
Range("A6") = strName2
Range("A7") = strName3
Range("A8") = strName4
Range("A9") = strName5
Range("A10") = strName6
Range("A11") = strName7
Range("A12") = strName8
Range("B5") = strPS1
Range("B6") = strPS2
Range("B7") = strPS3
Range("B8") = strDC1
Range("B9") = strDC2
Range("B10") = strDC3
Range("B11") = strDC4
Range("B12") = strDC5
Range("C3") = "01/02/2011"
Range("C3").Select
Selection.AutoFill Destination:=Range("C3:I3"), Type:=xlFillDefault
Range("C4") = Format(Range("C3"), "DDDD")
Range("C4").Select
Selection.AutoFill Destination:=Range("C4:I4"), Type:=xlFillDefault
With Range("C5")
    .Value = Range("A2")
    .Interior.Color = vbYellow
End With
Range("C5").Select
Selection.AutoFill Destination:=Range("C5:I5"), Type:=xlFillDefault
With Range("C8")
    .Value = Range("A1")
    .Interior.Color = 5287936
End With
Range("C8").Select
Selection.AutoFill Destination:=Range("C8:I8"), Type:=xlFillDefault
Columns("A:I").Columns.AutoFit

End Sub

 

[healey21]

Trevor,

Thank you for your assistance, this will be very useful.

Can I ask if there is a way to have a named range (I will look to call it Master) and then copy that range say 15 times with a 2 row gap?

I think this might be a better approach as the person who needs this, I can advice to create the weekly table, then name it and then run the code so it can copy the named range.

Any advice on this approach would be very helpful.