VB Code

[chrisnsmith]

What's wrong with this code?

Private Sub UpdateForm200()
If Cells(13, 2) = Today - 3 Then GoTo My_Procedure
ElseIf Cells(13, 2) = Today - 1 Then GoTo My_Procedure
End If

My_Procedure:
Sheets("Cus Futures").Select
Range(" H9:I50").Copy Range("D9:E50")
Range("F9:I50").ClearContents
Range("M2") = Date
'
'
Sheets("House Futures").Select
Range(" H9:I50").Copy Range("D9:E50")
Range("F9:I50").ClearContents
Range("M2") = Date

 

[Roger Govier]

Hi Chris

you are not saying what to do if the dates aren't 1 day or 3 days before
today.
Private Sub UpdateForm200()
If Cells(13, 2) = Today - 3 Then GoTo My_Procedure
ElseIf Cells(13, 2) = Today - 1 Then GoTo My_Procedure

Exit Sub

End If
your code

 

[Don Guillett]

today is for functions. Use DATE as you did in part of the code. also get
rid of selections.

If Cells(13, 2) = date - 3 or cells(13,2)=date-1 Then GoTo My_Procedure
My_Procedure:
with Sheets("Cus Futures")
. Range(" H9:I50").Copy . Range("D9:E50")
. Range("F9:I50").ClearContents
. Range("M2") = Date
end with
with Sheets("House Futures")
. Range(" H9:I50").Copy . Range("D9:E50")
. Range("F9:I50").ClearContents
. Range("M2") = Date
end with

 

[chrisnsmith]

I realized the code I posted wouldn't do what I wanted anyway. What I want
to do is run My_Procedure only on weekdays not weekends. I'm posting a new
code, will it do what I want?

Private Sub UpdateForm200()
If Weekday(Date) = vbSaturday Or Weekday(Date) = vbSunday Then
End If
ElseIf Cells(13, 2) = Date - 3 Or Cells(13, 2) = Date - 1 Then GoTo
My_Procedure
End If

My_Procedure:
With Sheets("Cus Futures")
Range(" H9:I50").Copy Range("D9:E50")
Range("F9:I50").ClearContents
Range("M2") = Date
End With
'
'
With Sheets("House Futures")
Range(" H9:I50").Copy Range("D9:E50")
Range("F9:I50").ClearContents
Range("M2") = Date
End With

 

[Don Guillett]

Try this instead. I noticed that you omitted the dots that are NECESSARY for
the with......... Put em back or WITH won't work.

Sub ttt()
If Weekday(Date) <> vbSaturday _
Or Weekday(Date) <> vbSunday Then
GoTo My_Procedure
End If
My_Procedure:
MsgBox "H"
End Sub

 

[Rick Rothstein]

The code in My_Procedure will be executed no matter what day of the week it
is for the that macro. As written, the code needs an Exit Sub statement
immediately in front of the My_Procedure: label. However, I probably would
just test the day of the week and exit the subroutine immediate if it is a
weekday. Something like this...

Sub Test()
If Weekday(Date, vbMonday) > 5 Then Exit Sub
' The My_Procedure code (label no longer needed) goes here
MsgBox "H"
End Sub

 

[Don Guillett]

Or, to use OP original style

Sub ttt()
If Weekday(Date) = vbSaturday _
Or Weekday(Date) = vbSunday Then
Exit Sub
End If

MsgBox "H"
End Sub