Auto Open Macro to find correct week tab & day/date in 52 week worksheets

I

Irene Bright

New Member
Joined
Feb 9, 2009
Messages
10
Hi,
I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily). I have pasted the code below & wonder if you can advise where I am going wrong. I would be happy to send the spreadsheet if needed! Thank you for your help. :eek:

Sub Auto_Open()
'
Dim week(52), currentday As Range
'
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"
week(25) = "WK25"
week(26) = "WK26"
week(27) = "WK27"
week(28) = "WK28"
week(29) = "WK29"
week(30) = "WK30"
week(31) = "WK31"
week(32) = "WK32"
week(33) = "WK33"
week(34) = "WK34"
week(35) = "WK35"
week(36) = "WK36"
week(37) = "WK37"
week(38) = "WK38"
week(39) = "WK39"
week(40) = "WK40"
week(41) = "WK41"
week(42) = "WK42"
week(43) = "WK43"
week(44) = "WK44"
week(45) = "WK45"
week(46) = "WK46"
week(47) = "WK47"
week(48) = "WK48"
week(49) = "WK49"
week(50) = "WK50"
week(51) = "WK51"
week(52) = "WK52"
'
Filname = Activeworkbook.FullName
'
If InStrRev(Filname, "\payroll.xls") = 0 Then

' --------------WK1---------------

Sheets("WK1").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK2---------------

Sheets("WK2").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK3---------------

Sheets("WK3").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK4---------------

Sheets("WK4").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK5---------------

Sheets("WK5").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK6---------------

Sheets("WK6").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK7---------------

Sheets("WK7").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK8---------------

Sheets("WK8").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK9---------------

Sheets("WK9").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK10---------------

Sheets("WK10").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK11---------------

Sheets("WK11").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK12---------------

Sheets("WK12").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' --------------WK13---------------

Sheets("WK13").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK14---------------

Sheets("WK14").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK15---------------

Sheets("WK15").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK16---------------

Sheets("WK16").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK17---------------

Sheets("WK17").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK18---------------

Sheets("WK18").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK19---------------

Sheets("WK19").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK20---------------

Sheets("WK20").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK21---------------

Sheets("WK21").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK22---------------

Sheets("WK22").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK23---------------

Sheets("WK23").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK24---------------

Sheets("WK24").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' --------------WK25---------------

Sheets("WK25").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK26---------------

Sheets("WK26").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK27---------------

Sheets("WK27").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK28---------------

Sheets("WK28").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK29---------------

Sheets("WK29").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK30---------------

Sheets("WK30").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK31---------------

Sheets("WK31").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK32---------------

Sheets("WK32").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK33---------------

Sheets("WK33").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK34---------------

Sheets("WK34").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK35---------------

Sheets("WK35").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK36---------------

Sheets("WK36").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' --------------WK37---------------

Sheets("WK37").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK38---------------

Sheets("WK38").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK39---------------

Sheets("WK39").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK40---------------

Sheets("WK40").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK41---------------

Sheets("WK41").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK42---------------

Sheets("WK42").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK43---------------

Sheets("WK43").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK44---------------

Sheets("WK44").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK45---------------

Sheets("WK45").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK46---------------

Sheets("WK46").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK47---------------

Sheets("WK47").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK48---------------

Sheets("WK48").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK49---------------

Sheets("WK49").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK50---------------

Sheets("WK50").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK51---------------

Sheets("WK51").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' -------------WK52---------------

Sheets("WK52").Select
'
Range("M2:S3").Select
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

' --- Locate current Week and day ---

' Select sheet
'today = Now
Sheets(week(DatePart("d", Now))).Select
' Select day and change colours
Set currentday = Range("L2:L3")
currentday.Offset(0, DatePart("w", Now)).Select
' Set colours
Selection.Font.ColorIndex = 2
Selection.Interior.ColorIndex = 5
Else
Sheets(week(DatePart("d", Now))).Select
End If

' ---- The end ----
End Sub
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
I'm not sure if I understood what you were after but here's my try:

Code: 
Private Sub Workbook_Open()
Dim WS As Worksheet
Dim WeekSheet As Worksheet
Dim WeekNumber As Integer
Dim DayNumber As Integer
Dim CurrentDay As Range

WeekNumber = DatePart("ww", Now)
DayNumber = DatePart("w", Now)

'Loop through all worksheets:
For Each WS In Worksheets
'if TabName starts with "WS" then do the changes:
    If UCase(Left(WS.Name, 2)) = "WS" Then
        With WS.Range("M2:S3")
            .Font.ColorIndex = 0
            .Interior.ColorIndex = 15
            .Interior.Pattern = xlSolid
        End With
    End If
    
    If UCase(WS.Name) = "WS" & WeekNumber Then
        Set WeekSheet = WS
    End If
Next WS

'Go to CurrentDay:
WeekSheet.Activate
Set CurrentDay = Range("L2:L3").Offset(0, DayNumber)

' Set colours:
    With CurrentDay
        .Font.ColorIndex = 2
        .Interior.ColorIndex = 5
    End With

CurrentDay.Select
End Sub

The workbook should always open (if the macros are enabled) with the current day on the "WS" & current weeknumber (using Excel weeknumbers) selected.
 
Upvote 0
Hi MISCA

Thanks for your reply to my query. I believe you are on the right track to what I am after, however I have copied/paste the content into Module 1 & unfortunately it doesn't work. I have tried to understand your code - but find some aspects difficult to follow. Can I explain more? Below is "WK43" tab (current) & I want the macro to open the workbook on the correct tab (of which there are 52 representing payroll weeks )& then look to the current date (columns M to S) & stop/highlight it in blue.

The dates in Column M to S have the following formulas "=+J2" in S & "=+S3-1" R back to M. The date in J2, being the week-end date, is also a formula "=+WK42!J2+7". The results have been formatted to show dates - but could this be the problem why the macro is not working?

Really do appreciate your taking time to look into this.;)

<TABLE style="WIDTH: 1086pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1448 x:str><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" span=7 width=55><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><TBODY><TR style="HEIGHT: 23.25pt; mso-height-source: userset" height=31><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 133pt; HEIGHT: 23.25pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #ece9d8" class=xl109 height=31 width=177 colSpan=2 x:str="WEEK ">WEEK </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 86pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #ece9d8" class=xl90 width=115 x:num>43</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 104pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #ece9d8" class=xl91 width=138></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 76pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #ece9d8" class=xl91 width=101></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 56pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #ece9d8" class=xl91 width=75></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 182pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #ece9d8" class=xl107 width=242 colSpan=3>Week Ending</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 89pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #ece9d8" class=xl108 width=118 colSpan=2 x:num="40055">24/01/2010</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 44pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #ece9d8" class=xl92 width=58>Day </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 41pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 width=55 x:fmla='=LEFT(TEXT(M2,"ddd"),1)'>M</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 41pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 width=55 x:fmla='=LEFT(TEXT(N2,"ddd"),1)'>T</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 41pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86 width=55 x:fmla='=LEFT(TEXT(O2,"ddd"),1)'>W</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 41pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86 width=55 x:fmla='=LEFT(TEXT(P2,"ddd"),1)'>T</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 41pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 width=55 x:fmla='=LEFT(TEXT(Q2,"ddd"),1)'>F</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 41pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 width=55 x:fmla='=LEFT(TEXT(R2,"ddd"),1)'>S</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 41pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 width=55 x:fmla='=LEFT(TEXT(S2,"ddd"),1)'>S</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 29pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl104 width=39> </TD></TR><TR style="HEIGHT: 24pt; mso-height-source: userset" height=32><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 62pt; HEIGHT: 24pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl93 height=32 width=82></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 71pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl94 width=95></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 86pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl94 width=115></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 104pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl94 width=138></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 76pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl94 width=101></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 56pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl94 width=75></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 52pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl95 width=69></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl95 width=79></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 71pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl95 width=94></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 46pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl95 width=61></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 43pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl96 width=57> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 44pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl96 width=58>Date </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl97 width=55 x:num="40049" x:fmla="=+N2-1">18/01</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl97 width=55 x:num="40050" x:fmla="=+O2-1">19/01</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl97 width=55 x:num="40051" x:fmla="=+P2-1">20/01</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl97 width=55 x:num="40052" x:fmla="=+Q2-1">21/01</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl97 width=55 x:num="40053" x:fmla="=+R2-1">22/01</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl97 width=55 x:num="40054" x:fmla="=+S2-1">23/01</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl97 width=55 x:num="40055" x:fmla="=+J1">24/01</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 29pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl105 rowSpan=2 width=39></TD></TR><TR style="HEIGHT: 35.25pt; mso-height-source: userset" height=47><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 35.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76870 class=xl87 height=47 width=82 x:autofilterrange="$A$3:$S$3" x:autofilter="all"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76871 class=xl87 width=95 x:str="Client " x:autofilter="all"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76872 class=xl87 width=115 x:autofilter="all"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 104pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76873 class=xl87 width=138 x:autofilter="all"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76874 class=xl87 width=101 x:autofilter="all"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76875 class=xl87 width=75 x:autofilter="all"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76876 class=xl87 width=69 x:autofilter="all"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76877 class=xl87 width=79 x:autofilter="all"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76878 class=xl87 width=94 x:autofilter="all"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76879 class=xl88 width=61 x:autofilter="all"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" id=_x0000_s76880 class=xl89 width=57 x:autofilter="all"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76881 class=xl87 width=58 x:autofilter="all"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76882 class=xl84 width=55 x:autofilter="all"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76883 class=xl84 width=55 x:autofilter="all"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76884 class=xl84 width=55 x:autofilter="all"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76885 class=xl84 width=55 x:autofilter="all"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76886 class=xl84 width=55 x:autofilter="all"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76887 class=xl84 width=55 x:autofilter="all"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=_x0000_s76888 class=xl84 width=55 x:autofilter="all"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 height=17 width=82> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 width=95> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=115> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 104pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=138> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=101> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=75> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=69> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=79> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl99 width=94> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=61> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=57> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=58> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl101 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 29pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl101 width=39> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 height=17 width=82> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 width=95> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=115> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 104pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=138> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=101> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=75> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=69> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=79> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl99 width=94> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=61> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=57> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=58> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl102 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 29pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl102 width=39> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 height=17 width=82> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 width=95> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=115> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 104pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=138> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=101> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=75> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=69> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=79> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl99 width=94> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=61> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=57> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=58> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl103 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl101 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 29pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl101 width=39> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 height=17 width=82> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 width=95> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=115> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 104pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=138> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=101> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=75> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=69> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=79> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl99 width=94> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=61> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=57> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 width=58> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl101 width=55> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 29pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl101 width=39> </TD></TR></TBODY></TABLE>
 
Upvote 0
You said you pasted the macro to Module1? Event macros should not be placed in normal modules but in the worksheet or workbook "modules" (don't know a better word for it). This one should go to the Workbook module: In VBA Editor you should see the workbook structure in the left. Double click where it says "Workbook" and paste the macro there. Make sure you don't get extra sub-events before the macro code because they might cause you problems.

Save your workbook and try to open it. Should work.

The formulas & values that you have in your sheet have no effect on the macro. The only thing it looks from any tab is the sheetname: first is looks for sheets starting with "WS" and colors the given range grey. Then it checks if the sheetname happens to be "WS" & WeekNumber (today is week 3 so it's looking for sheet named "WS3"). Today is Tuesday and it's the 3rd day of the week according to Excel (Sunday = 1... Saturday=7) so the macro starts counting from Column L and the 3rd column is M. The active range should be M2:M3 (on tab named "WS3") when the workbook opens today.

I did not write any error handlers (=usually the hardest part if you ask me) so the macro will crash if it can't find the sheet it's looking for. Might be a good thing to change the code a bit so it won't crash.

Change the beginning of the previous code to

Code: 
WeekNumber = DatePart("ww", Now)
DayNumber = DatePart("w", Now)

Set WeekSheet = ActiveSheet       'Added this line

'Loop through all worksheets:

If the macro doesn't find the tab name it's looking for it'll open on the page it was last saved on.
 
Upvote 0
Made a couple of minor corrections to the beginning of the code as well:

Code: 
WeekNumber = DatePart("ww", Now, , vbFirstFourDays)   'Changed this to use the "correct" week numbering
DayNumber = DatePart("w", Now, vbMonday)     'Changed this line: Monday=1

Set WeekSheet = ActiveSheet       'Added this line

'Loop through all worksheets:

1 ) I changed the DayNumbering to start from Monday (Tuesday opens in column N, not O as before - I don't know where I got the M from because that's wrong either way).

2) Also changed the WeekNumber a bit to match the calendar weeknumbering (= Week 1 is the week where the first Thursday of the year is = "FirstFourDays")
 
Upvote 0
Hi again Misca!

I followed your instructions & inserted the code into the ThisWorkbook in the VBA Project & deleted code from Module 1. I have further code in Module 2 that records sorting & filtering of data - but I trust this will not affect the opening macro.

I saved the file & opened it up, but an error occured & stoped at the very last line "CurrentDay.Select. Can I just check that my worksheets named "WK1" up to "WK52" do not conflict with the "WS" you refer to? Also "WK1" contains dates (Columns M to S) ending with the first Sunday (Column S) in April of any year. Year runs from April to March for payroll.

I have both Excel 2003 & 2007 & working on Windows XP & wondered if this is relevant. I do apologise if I have not provided you with clear information, but hope that you can help me get to the bottom of this.:confused:

Just to confirm the last code tested (with modifications) is as hereunder:-

Private Sub Workbook_Open()
Dim WS As Worksheet
Dim WeekSheet As Worksheet
Dim WeekNumber As Integer
Dim DayNumber As Integer
Dim CurrentDay As Range
'Changed this to use the "correct" week numbering
WeekNumber = DatePart("ww", Now, , vbFirstFourDays)
'Changed this line: Monday=1
DayNumber = DatePart("w", Now, vbMonday)
'Added this line
Set WeekSheet = ActiveSheet
'Loop through all worksheets:
For Each WS In Worksheets
'if TabName starts with "WS" then do the changes:
If UCase(Left(WS.Name, 2)) = "WS" Then
With WS.Range("M2:S3")
.Font.ColorIndex = 0
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
End With
End If

If UCase(WS.Name) = "WS" & WeekNumber Then
Set WeekSheet = WS
End If
Next WS
'Go to CurrentDay:
WeekSheet.Activate
Set CurrentDay = Range("L2:L3").Offset(0, DayNumber)
' Set colours:
With CurrentDay
.Font.ColorIndex = 2
.Interior.ColorIndex = 5
End With
CurrentDay.Select
 
Upvote 0
Irene Bright said:
Hi again Misca!

Can I just check that my worksheets named "WK1" up to "WK52" do not conflict with the "WS" you refer to?
Click to expand...

Hi Irene. I think that if you changed the relevant "WS in Misca's code to your "WK" as I think it is simple coding reference error.

You do it by selecting the code in VB editor and going Ctrl + H for search & replace but be careful what you change (what you actually select to change) and only do it for that slection or that procedure/macro. If you do make a mistake udo works as well & as always do it on a copy workbook 1st.
 
Upvote 0
Hi Lionelnz,

Thanks for your message. I did infact try this change (my initial query indicated tab names - hence why I was unsure), but message "compile error: Expected End Sub" still appears on the final line "CurrentDay.Select". Any further ideas? :confused:

To confirm, last tested code reads:-

Private Sub Workbook_Open()
Dim WK As Worksheet
Dim WeekSheet As Worksheet
Dim WeekNumber As Integer
Dim DayNumber As Integer
Dim CurrentDay As Range
'Changed this to use the "correct" week numbering
WeekNumber = DatePart("ww", Now, , vbFirstFourDays)
'Changed this line: Monday=1
DayNumber = DatePart("w", Now, vbMonday)
'Added this line
Set WeekSheet = ActiveSheet
'Loop through all worksheets:
For Each WK In Worksheets
'if TabName starts with "WK" then do the changes:
If UCase(Left(WK.Name, 2)) = "WK" Then
With WK.Range("M2:S3")
.Font.ColorIndex = 0
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
End With
End If
If UCase(WK.Name) = "WK" & WeekNumber Then
Set WeekSheet = WK
End If
Next WK
'Go to CurrentDay:
WeekSheet.Activate
Set CurrentDay = Range("L2:L3").Offset(0, DayNumber)
' Set colours:
With CurrentDay
.Font.ColorIndex = 2
.Interior.ColorIndex = 5
End With
CurrentDay.Select
 
Upvote 0
Hi Lionelnz,

Just realised I missed off "End Sub" at the end. Apologies for this oversight.

Macro now runs on opening, but stops on the incorrect week. It is selecting week 3 & not Week 43. However, it does appear to select Wednesday, although the date is 15/04/09 & not today's date!

Can you advise further?:confused:

Many thanks,
Irene
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
Vba Macro Code running partially when i trying it from another worksheet
Replies
3
Views
673
HaHoBe
HaHoBe
A
  • Solved
Sumif not working on my macros but working when i am pressing F8
Replies
3
Views
480
Zot
Z
M
  • Question
VBS Crash
Replies
1
Views
685
Alex Blakenburg
A
S
  • Solved
How do I make the 2nd macro execute the formatting and pasting on the subsequent values that is added by the 1st macro.
2 3
Replies
24
Views
2K
johnnyL
J
K
  • Solved
Create macro that automatically goes to current date from a monthly calendar on each tab when file is opened
Replies
3
Views
928
KimC33
K

Forum statistics

Threads
1,218,027
Messages
6,140,043
Members
450,253
Latest member
MJ Papa

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top