First Day Of Next Month 2

[EliseFreedman]

How do I insert the First Day of Next Month Into a footer automatically
I have got the following code which programatically inserts a footer onto each page of the worksheet and then prompts the user for how many copies are to be printed.
However, the code falls down when it is trying to get the FooterDate. It displays a syntax error. Can anyone see whats wrong. YEAR is highlighted. The same formula works fine when used in a cell

Private Sub CommandButton1_Click()
Application.EnableEvents = False
Sheets("Pricelist").Select
Application.ActivePrinter = "\\LINDA\Canon iR2200-3300 PCL5e on Ne06:"
Dim NoofCopies As Long
Dim FooterDate As Long

FooterDate = DATE(YEAR(Now()),MONTH(Now())+1,1)
Dim PgCnt As Long
PgCnt = 0
NoofCopies = 0
PgCnt = Application.ExecuteExcel4Macro("Get.Document(50)")
NoofCopies = InputBox("How Many Copies Would You Like To Print", "Blackspur Price Lists")
With ActiveSheet
.PageSetup.LeftFooter = "Updated " & FooterDate

.PageSetup.RightFooter = "&P" ' inserts page number
 ActiveWindow.SelectedSheets.PrintOut From:=1, To:=PgCnt, Copies:=NoofCopies, Collate:=True
End With
Cancel = True
Application.EnableEvents = True
End Sub

 

[SkipVought]

Elsie,

Dates are NUMBERS -- like today is 38084

When you enter 4/7/2004 into a cell, Excel is 'smart' enough to recognize that you have entered a DATE FORMAT, converts the STRING to a NUMBER and then FORMATS the cell in the default DATE FORMAT.

Your FooterDate needs to be STRING

Dim FooterDate as String
...
FooterDate = Format(Now, "mm/dd/yyyy")

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

Hi EliseFreedman,

The Date Function does not take any arguments, and returns the current date. The function you need to return the first of next month is DateSerial. This will return a number which you will then need to Format (as a string) to print out as you want it. You can change the declaration of FooterDate as Skip suggests ..

[blue]Dim FooterDate as String
:
:
FooterDate = [red]Format([/red]DATE[red]Serial[/red](YEAR(Now()),MONTH(Now())+1,1)[red],"dd mmm yyyy")[/red][/blue]

.. or you can leave it as is and format the date when you build the footer ..

[blue]FooterDate = DATE[red]Serial[/red](YEAR(Now()),MONTH(Now())+1,1)
:
:
.PageSetup.LeftFooter = "Updated " & [red]Format([/red]FooterDate[red],"Short Date")[/red][/blue]

(using whatever format you want, of course).


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Acdel]

Try DateAdd("d",-1,DateAdd("m",1,now))

 

[TonyJollans]

Hi Acdel,

That won't give the first of the month (today, April 7, it returns May 6).

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.