Entering a date manually

Posted by merlin on January 02, 2001 1:01 PM

Hi all. I making a macro that will import a file(s) and from that file create a pivot table and then allow the user to print reports. But the reports are for the end of each month. Is there a way i can have the report display the end of the month date in a header, each month when the files are refreshed?

Or should i prompt the user to enter the period ending (i.e. 12/31/2000) and whatever they enter, post that in the "date" header automatically? If so how could i prompt the user for a 'date' and then place that in a cell?

thanks for your help in advance,
Brian

Posted by Dave on January 02, 2001 3:12 PM

Hi Brian

Could you use some code like this in the Workbook Open event:

Private Sub Workbook_Open()
Dim LastDateOfMonth
LastDateOfMonth = DateAdd("d", -1, DateSerial(Year(Date), Month(Date) + 1, 1))
If Date = LastDateOfMonth Then
Sheets("Sheet1").Range("A1") = LastDateOfMonth
End If
End Sub

Dave
OzGrid Business Applications

Posted by Dave on January 02, 2001 3:19 PM

OzGrid Business Applications