The added If statement at the beginning will test for the
current date being Monday, If it is, it will then check
Cell M2 for the previous Friday's date, If it is, it will
Copy the range and then clear it. If it is not Monday, it
will then check to see if it is a day from Tuesday thru
Thrusday and do the copy and clear. If the criteria is not
met in either part of the If...EsleIf statement, it will not
do the copy and clear.


Sheets("Cus Futures").Select
If Format(Date, "w") = "2" And Range("M2") = Date - 3 Then
Range(" H9:I50").Copy Range("D9:E50")
Range("F9:I50").ClearContents
Range("M2") = Date
ElseIf Weekday(Date, vbMonday) < 6 And Range("M2") = _
(Date - 1) Then
Range(" H9:I50").Copy Range("D9:E50")
Range("F9:I50").ClearContents
Range("M2") = Date
End If

"chrisnsmith" wrote:

I have asked a similar question before but I realized I have more paramaters
I need to cover. I have to save and email my workbook daily and it is
sometimes necessary to open the saved workbooks to verify the info I email.

I have three problems I need to solve.

1. I need this to run only on weekdays.
2. My code ('M2") = (Date - 1) doesn"t take into account weekends. So it
won't work on Mondays. I need to correct this.
3. I don't want this code to run when I open an older saved workbook.

Can anyone help?

Sheets("Cus Futures").Select
If Weekday(Date, vbMonday) < 6 And Range("M2") = (Date - 1) Then
Range(" H9:I50").Copy Range("D9:E50")
Range("F9:I50").ClearContents
Range("M2") = Date
End If

There is a problem with the Elself statement. When I try to run the code
I get a message that says sub or function is not defined and the word (Range)
is highlighted. What's wrong, and how do I fix it?

"JLGWhiz" wrote:

The added If statement at the beginning will test for the
current date being Monday, If it is, it will then check
Cell M2 for the previous Friday's date, If it is, it will
Copy the range and then clear it. If it is not Monday, it
will then check to see if it is a day from Tuesday thru
Thrusday and do the copy and clear. If the criteria is not
met in either part of the If...EsleIf statement, it will not
do the copy and clear.


Sheets("Cus Futures").Select
If Format(Date, "w") = "2" And Range("M2") = Date - 3 Then
Range(" H9:I50").Copy Range("D9:E50")
Range("F9:I50").ClearContents
Range("M2") = Date
ElseIf Weekday(Date, vbMonday) < 6 And Range("M2") = _
(Date - 1) Then
Range(" H9:I50").Copy Range("D9:E50")
Range("F9:I50").ClearContents
Range("M2") = Date
End If

"chrisnsmith" wrote:

I have asked a similar question before but I realized I have more paramaters
I need to cover. I have to save and email my workbook daily and it is
sometimes necessary to open the saved workbooks to verify the info I email.

I have three problems I need to solve.

1. I need this to run only on weekdays.
2. My code ('M2") = (Date - 1) doesn"t take into account weekends. So it
won't work on Mondays. I need to correct this.
3. I don't want this code to run when I open an older saved workbook.

Can anyone help?

Sheets("Cus Futures").Select
If Weekday(Date, vbMonday) < 6 And Range("M2") = (Date - 1) Then
Range(" H9:I50").Copy Range("D9:E50")
Range("F9:I50").ClearContents
Range("M2") = Date
End If

Not sure what is causing it to error on your system. It runs fine on mine.
Check for typos in the spelling, missing Quote marks, etc.

"chrisnsmith" wrote:

There is a problem with the Elself statement. When I try to run the code
I get a message that says sub or function is not defined and the word (Range)
is highlighted. What's wrong, and how do I fix it?

"JLGWhiz" wrote:

The added If statement at the beginning will test for the
current date being Monday, If it is, it will then check
Cell M2 for the previous Friday's date, If it is, it will
Copy the range and then clear it. If it is not Monday, it
will then check to see if it is a day from Tuesday thru
Thrusday and do the copy and clear. If the criteria is not
met in either part of the If...EsleIf statement, it will not
do the copy and clear.


Sheets("Cus Futures").Select
If Format(Date, "w") = "2" And Range("M2") = Date - 3 Then
Range(" H9:I50").Copy Range("D9:E50")
Range("F9:I50").ClearContents
Range("M2") = Date
ElseIf Weekday(Date, vbMonday) < 6 And Range("M2") = _
(Date - 1) Then
Range(" H9:I50").Copy Range("D9:E50")
Range("F9:I50").ClearContents
Range("M2") = Date
End If

"chrisnsmith" wrote:

I have asked a similar question before but I realized I have more paramaters
I need to cover. I have to save and email my workbook daily and it is
sometimes necessary to open the saved workbooks to verify the info I email.

I have three problems I need to solve.

1. I need this to run only on weekdays.
2. My code ('M2") = (Date - 1) doesn"t take into account weekends. So it
won't work on Mondays. I need to correct this.
3. I don't want this code to run when I open an older saved workbook.

Can anyone help?

Sheets("Cus Futures").Select
If Weekday(Date, vbMonday) < 6 And Range("M2") = (Date - 1) Then
Range(" H9:I50").Copy Range("D9:E50")
Range("F9:I50").ClearContents
Range("M2") = Date
End If

Thanks, I did and found that in the first Elseif statement I left out a
parenthesis.

"JLGWhiz" wrote:

Not sure what is causing it to error on your system. It runs fine on mine.
Check for typos in the spelling, missing Quote marks, etc.

"chrisnsmith" wrote:

There is a problem with the Elself statement. When I try to run the code
I get a message that says sub or function is not defined and the word (Range)
is highlighted. What's wrong, and how do I fix it?

"JLGWhiz" wrote:

The added If statement at the beginning will test for the
current date being Monday, If it is, it will then check
Cell M2 for the previous Friday's date, If it is, it will
Copy the range and then clear it. If it is not Monday, it
will then check to see if it is a day from Tuesday thru
Thrusday and do the copy and clear. If the criteria is not
met in either part of the If...EsleIf statement, it will not
do the copy and clear.


Sheets("Cus Futures").Select
If Format(Date, "w") = "2" And Range("M2") = Date - 3 Then
Range(" H9:I50").Copy Range("D9:E50")
Range("F9:I50").ClearContents
Range("M2") = Date
ElseIf Weekday(Date, vbMonday) < 6 And Range("M2") = _
(Date - 1) Then
Range(" H9:I50").Copy Range("D9:E50")
Range("F9:I50").ClearContents
Range("M2") = Date
End If

"chrisnsmith" wrote:

I have asked a similar question before but I realized I have more paramaters
I need to cover. I have to save and email my workbook daily and it is
sometimes necessary to open the saved workbooks to verify the info I email.

I have three problems I need to solve.

1. I need this to run only on weekdays.
2. My code ('M2") = (Date - 1) doesn"t take into account weekends. So it
won't work on Mondays. I need to correct this.
3. I don't want this code to run when I open an older saved workbook.

Can anyone help?

Sheets("Cus Futures").Select
If Weekday(Date, vbMonday) < 6 And Range("M2") = (Date - 1) Then
Range(" H9:I50").Copy Range("D9:E50")
Range("F9:I50").ClearContents
Range("M2") = Date
End If