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.

Use the CASE statement with separate sections for each month.
--
Gary's Student
gsnu200701


"Marie Bayes" wrote:

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.

Thanks Gary's student, but I'm not that clever. I was hoping someone could
actually give me the code that would tell me what last month was and then how
I could 'jump' to that part of my macro.

"Gary''s Student" wrote:

Use the CASE statement with separate sections for each month.
--
Gary's Student
gsnu200701


"Marie Bayes" wrote:

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.

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.

For example:

Sub marie()
Dim d As Date
Dim n As Integer
d = Now
n = Month(d) - 1
If n = 0 Then n = 12

Select Case n
Case 1
MsgBox ("january")
Case 2
MsgBox ("february")
Case 3
MsgBox ("march")
Case 4
MsgBox ("april")
Case 5
MsgBox ("may")
Case 6
MsgBox ("june")
Case 7
MsgBox ("july")
Case 8
MsgBox ("august")
Case 9
MsgBox ("september")
Case 10
MsgBox ("october")
Case 11
MsgBox ("november")
Case 12
MsgBox ("december")
End Select
End Sub

When this is run, it will announce the name of the previous month. After
the MSGBOX(), include any coding specific for that month.
--
Gary's Student
gsnu200701


"Marie Bayes" wrote:

Thanks Gary's student, but I'm not that clever. I was hoping someone could
actually give me the code that would tell me what last month was and then how
I could 'jump' to that part of my macro.

"Gary''s Student" wrote:

Use the CASE statement with separate sections for each month.
--
Gary's Student
gsnu200701


"Marie Bayes" wrote:

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.

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.

Hi
Thanks for that, I've modified the code slightly to give me the month for
yesterday's date - thanks so much. I'm about to try out the code to see if I
can manage to runit using your example. Thanks again.

"Gary''s Student" wrote:

For example:

Sub marie()
Dim d As Date
Dim n As Integer
d = Now
n = Month(d) - 1
If n = 0 Then n = 12

Select Case n
Case 1
MsgBox ("january")
Case 2
MsgBox ("february")
Case 3
MsgBox ("march")
Case 4
MsgBox ("april")
Case 5
MsgBox ("may")
Case 6
MsgBox ("june")
Case 7
MsgBox ("july")
Case 8
MsgBox ("august")
Case 9
MsgBox ("september")
Case 10
MsgBox ("october")
Case 11
MsgBox ("november")
Case 12
MsgBox ("december")
End Select
End Sub

When this is run, it will announce the name of the previous month. After
the MSGBOX(), include any coding specific for that month.
--
Gary's Student
gsnu200701


"Marie Bayes" wrote:

Thanks Gary's student, but I'm not that clever. I was hoping someone could
actually give me the code that would tell me what last month was and then how
I could 'jump' to that part of my macro.

"Gary''s Student" wrote:

Use the CASE statement with separate sections for each month.
--
Gary's Student
gsnu200701


"Marie Bayes" wrote:

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.

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.