Do you want ALL of those ClearContents to run when the UpdateForms procedure
is called, or only a SINGLE one which you specify? For instances, in your
example...

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then Call UpateForms

if the condition is met, do you want all the sheets processed (contents
cleared) or only the "Cus Futures" one?

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
This is the procedure I want to Call. I've only listed 6 for simplicty,
but
I have 31 worksheets total.
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
'
'
Sheets("Cus Calls").Select
Range("G9:H50").Copy Range("C9:D50")
Range("E9:J50").ClearContents
Range("J2") = Date
'
'
Sheets("Cus Puts").Select
Range("G9:H50").Copy Range("C9:D50")
Range("E9:J50").ClearContents
Range("J2") = Date
'
'
Sheets("House Options").Select
Range("H9:I50").Copy Range("D9:E50")
Range("F9:K50").ClearContents
Range("K2") = Date
'
'
Sheets("Cus Indexes").Select
Range("F9:G50").Copy Range("B9:G50")
Range("D9:K50").ClearContents
Range("K2") = Date

What I want to do is place this in a separate module named UpdateForms and
call it from the main module.
For example If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then Call UpateForms.


"Rick Rothstein" wrote:

I'm guessing UpdateForms is a subroutine (you didn't show us your
complete
code), although I am not sure how self-contained it is (does it have an
argument list?) or what its dependencies are (for example, do you have
your
ranges qualified or not?). Will other subroutines, functions and/or event
procedures from other Modules (worksheet code windows included) call it
or
will it be called only from one worksheet code window? If only one...
then
just put UpdateForms in that code window too. If more than one... click
Insert/Module and put it in that code window (but now you will have to
qualify your ranges with worksheet references, probably ActiveWindow, but
that is only a guess without seeing your code). Where you put code, and
how
you structure it, depends on how it is to be used... you haven't really
given us enough to know what your intentions are. As for calling it from
any
other procedure... just use its name (and pass any arguments it may have
into it). You might want to look up the Sub Statement and the Function
Statement in VB's help files to get a better handle on calling
subroutines
and functions.

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
Ok, I made the changes to the Elseif statement.

Now, how do I put my procedure(UpdateForms) in a separate module and
have
it
called from the (This Workbook) module?

"Rick Rothstein" wrote:

You can't do it that way... you can't do a double test in a single
logical
expression. However, VB's Weekday function calculate starting with any
day
of the week, so let's start with Tuesday. Give this ElseIf statement a
try...

ElseIf Weekday(Date, vbTuesday) < 5 And Worksheets("Cus
Futures").Cells(1,
1).Value = Date -1 Then

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
If it is Tuesday thru Friday

"Rick Rothstein" wrote:

Something is wrong with the first part of your ElseIf statement,
namely
this
part...

Weekday(Date,vbMonday) = 2 < 7

What is "equal 2, less than 7" supposed to mean? What test are you
trying
to
perform?

--
Rick (MVP - Excel)


"chrisnsmith" wrote in
message
...
I'm having a problem with the If and Elseif functions.
When I enter the following everything works fine.


If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then


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


ElseIf Weekday(Date,vbMonday) = 2 < 7 And Worksheets("Cus
Futures")
_
.Cells(1, 1).Value = Date -1 Then

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


I only list two worksheets for simplicities sake, however I have
31
worksheets
that need to be updated daily. So I have placed all worksheets to
update
in
a module called UpdateForms and did the following.

I changed my If statement to;
If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
End if

When I run this I get a message End If without a block If.

I changed both my If statement and Elseif statement to:

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
Elseif Weekday(Date, vbMonday) =2 < 7 And Worksheets("Cus
Futures")
_
.Cells (1, 1).Value = Date - 1 Then Call UpdateForms

When I run this code I get a similar message Else without an If.

What am I doing wrong and how do I correct it?