On Mon, 23 Nov 2009 15:06:01 -0800, Ayo wrote:

How do I ensure that, in the "ElseIf Weekday(Date) 2 Then" statement of
the sub below "ws.Range("J6").Value" will always be the Tuesday of the week
of "ws.Range("F7").Value".


Private Sub Workbook_Open()
Dim ws As Worksheet
Const PWORD As String = "Ayo"
Application.ScreenUpdating = False

If Me.Name = "Northeast AAV Project Outlook_ver2.xls" Then
If Weekday(Date) = 2 Then
For Each ws In Worksheets
If ws.Name < "BO Download" Then
ws.Visible = True
ws.Range("F7").Value = Date
ws.Range("J6").Value = Date + 1
End If
Next
ElseIf Weekday(Date) 2 Then
For Each ws In Worksheets
If ws.Name < "BO Download" Then
ws.Visible = True
ws.Range("F7").Value = Date
ws.Range("J6").Value = Date + 1
End If
Next
End If
Else
Exit Sub
End If
Application.ScreenUpdating = True
End Sub


Perhaps (assuming your week starts on Sunday):

ws.Range("J6").Value = Date + 3 - weekday(date)
--ron