Marie,

The Month statement will get the current month

Month(Date)

but I tried to anticipate a couple of things, first that you don't want the
code to run more than once in the month, that you cannot guarantee that the
1st day of the month is a workday, and that January - 1 is 0, not 12.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Marie Bayes" wrote in message
...
Hi Bob, you always come to my rescue!
Allow me to explain a little further, I don't think I was very clear at
first. I have a spreadsheet that pulls data from elsewhere; at the end of
each month I need the data to be input to a new tab 'Feb'. In Crystal
Reports I would just identify the new month by 'currentdate -1' (to
identify
yesterday), if the month is, eg, 02, put the data in a new sheet, if it's
03,
it goes to another sheet etc. I have the whole thing running
automatically
but currently I'd have to change all the macros at the end of the month
and
I'm after a way not to do this. So is there an expression in VB that
will
identify the current month, so that I can then jump to the relevant part
of
my macro. I hope that makes sense, am I expecting too much??
"Bob Phillips" wrote:

This will work by checking the last run date on a specified sheet, the
code
allows you to change my settings, and if it is a change of month, it
runs
the appropriate macro. It assumes that the macros are called macro_Jan,
macro_Feb, etc.

Private Sub Workbook_Open()
Const SH_DATA As String = "Sheet1" '<=== change to suit
Const WS_CELL As String = "H1" '<=== change to suit

If Val(Format(Date, "yyyymm")) _
Val(Format(Worksheets(SH_DATA).Range(WS_CELL).Valu e, "yyyymm"))
Then
Application.Run "macro_" &
Format(Worksheets(SH_DATA).Range(WS_CELL).Value, "mmm")
Worksheets(SH_DATA).Range(WS_CELL).Value = Date
End If

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

Thge m onth macros, macro_Jan etc., should be placed in a standard code
module.

You will also need to store last month's date in the data cell, else the
code never runs.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Marie Bayes" wrote in message
...
I need to run a macro over a year, I would like to set it up at the
start
of
the year but set each part to run dependant on what month I'm in. I
always
run the report for data entered up to yesterday, ie, if it's the 1st
of
Feb
will be running Jan's macro. Is there a way I can write into the code
to
call the section just for that month and then at the end of that
section
continue with the remainder of the macro? Thanks in advance for your
help.