print and save by date

[sivadnitram]

Good Afternoon,
I am using Office 2003 and XP Home edtion. Version 2002 service pack 3
I am trying to construct a workbook to keep track of credit cards and bank account. The sheets that do all the calculations work fine. The first two sheets are named FRONT and SUMMARY. on the FRONT sheet cell A1 holds the date derived from =TODAY function and is formatted as 12-Sep. I have a recorded macro entitled PRINT which runs OK from the button entitled PRINTSHEET on the FRONT sheet. I am trying to get this macro to run whenever cell A1 holds a date which co-incides with the last day of any month. I have tried reformatting the date using =DAY(reference cell) and =Month(reference cell) the result being displayed in cell A2. I have tried to use the value in A2 to trigger the PRINT macro using:-

[VBA]If Me.Range("A2").Value = ** Then
Call printsheet
End If[/VBA]

Which was very kindly provided by Simon Lloyd. I cannot seem to get passed the fact that different dates end up giving me identical values in A2 which makes the whole thing wobble. As a novice I expect there id a very simple solution but I cannot find it. I have consulted the excel programme help files, microsoft office online and Excel 2003 bible to try to solve this problem. If there is anybody who can help me I would be very grateful.
Sivadnitram

[Bob Phillips]

If the value in A1 is say 30th September, that value could be there for an awfully long time. So what would be the trigger for your macro running, when that date is input? If so you could use

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If IsDate(.Value) Then

If Not Month(.Value) = Month(.Value + 1) Then

Call MyMacro
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[sivadnitram]

Hi
to be perfectly honest i had not thought that a day (i.e.30 September) woulod have a finite time. I did say I' a novice. I suppose I really want the sheets printed at the conclusion of each month. Therefore to print for september should be when the date value changes to 1st Oct. If that effects what has already been suggested what changes would I have to make

regards

Sivadnitram

[Bob Phillips]

Print what for September.

[sivadnitram]

Hi
What I would like to do is to run MyMacro on the 1st day of each month the moment the date changes i.e.post midnight. I have installed the code you so kindly supplied and at the moment I am trying to fixure out what each lines does. If I haven't fixured it out by my next birthday (64th) I post you a request. Thanks for help thus far

sivadnitram

[Bob Phillips]

To get the code to run on the first, you need some trigger. Do you open it once a day, becuase that could be a trigger, or do you leave it open at all times?

Maybe the simplest way is to use Ontime, like so

[vba]

Public Sub MyMacro()
Dim RunTime As Date

'your basic code

RunTime = DateSerial(Year(Date), Month(Date) + 1, 1)
Application.OnTime RunTime, "MyMacro"
End Sub
[/vba]

[sivadnitram]

Good Morning.

the computer on which I wish to print is opened daily but is not left on each day. Thank you for the time you have spent helping me. As one new to excel in particular and computers generally it is comforting to know there are people willing to help. At my age (63) learning curves tend to be stepper and one tends to fall off it more often.

Thanks for all your help

sivadnitram

[Bob Phillips]

The Ontime won't work when opened daily, because th Ontime gets wiped out. We could add it to the workbook_open, but if you open this workbook many times in one day, it will run every time unless we add a marker. What is the situation in your case?

One thing you will learn, Excel can do ANYTHING!

[sivadnitram]

Thanks very much for the all help. The macro is running OK and doing just what I want. All i need now is to figure out excactly how the routine triggers the macro. I can figure out most of it just some lines I do not understsand. I am sure that to one such as you its like a second language (which indeed it is) but please do not tell me what it means as i want to figure it out myself. I am realy glad i found this site especially the Kbase.

Many thanks again

Sivadnitram