Auto Open Macro - Criteria Week & day/date location

[Irene Bright]

Hi,
I am new to VBA & not sure of the full understanding of code copied that worked for Monthly (12) tabs; however I thought if modified to show weeks, the macro would be able to locate the current day/date - but upon opening, the cell stops at WK17 & column AC - rather than WK42, Column S (this will change upon current date). I have pasted the code below & wonder if you can advise where I am going wrong. Thank you for your help.
　　
Sub Autpen()
　　
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("d", 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
http://www.eset.com/