Calculate Date in Footer

[Musette]

I am not more than a casual user of Excel (which is perhaps why I can't
figure out how to do this).

I would like to have a date automatically entered in the footer (which I
know how to do)...but then have it automatically update itself 7 days later.

In other words, I need to have Monday's date automatically updated and
entered each week.

If there is a solution for how to do this, I would appreciate your help.
....Just remember that I don't "do" formulas and functions and macros more
than once every few years.

 

[Gord Dibben]

In Thisworkbook module.

Private Sub Workbook_Open()
'or beforeprint
'Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim MyWeekDay
MyWeekDay = Weekday(Date)
If MyWeekDay = vbMonday Then 'or 2
With ActiveSheet
.PageSetup.CenterFooter = Format(Date, "dddd mmmm dd, yyyy")
End With
End If
End Sub


Gord Dibben MS Excel MVP

 

[Musette]

Hi, Gord

Thank you for responding so quickly to my question. Unfortunately, for me,
I can't make heads or tails out of your answer. I think it's some kind of
macro, but I don't know what to do with it.

Can I just copy and paste it? Where do I go once I have my file open?

I'm reluctant to ask you for more help, but I really meant it when I said
that I know a lot less than a little!

If you could write directions for a six year old, I think I might be able to
do it.

Thanks again.

Marsha

 

[Mike Rogers]

Musette

The "Thisworkbook module is the place you will copy and paste the code Gord
gave you. To find this go if you are using excel 2003 or older (basically
anthing but 2007, and it might be the same in 2007 but I don't know) to the
Excel icon in the upper left corner of your worksheet. Right click on this
icon and select "View Code". This will bring up the MS Visual Basic window.
On the left side there might be a smaller window that is labeled "Project -
VBAProjest" One of the + Plus signs will be expanded, it will say
VBAProject(YourSavedWorkbookName). In this tree you will see the sheet
numbers and names followed by Thiswoorkbook. This will have a light grey
background around it. If this is what you are seeing the window to the right
is the area you will paste the code. Then close this window using the Big
red X in the upper right corner. You should be ready to fly!!! If by chance
you still have a problem post back in this thread and gord will probibly give
you some more help, just remember you are moving in the right direction, and
if you need more help just ask!!!

Mike Rogers

 

[Gord Dibben]

Right-click on the Excel Icon left of "File" on the menubar and "View Code"
should open up Thisworkbook module directly.

Paste the code into that module.

Notes on the code............when the workbooks is opened the code checks to see
if this is Monday.

If it is, it adds the footer.

If not Monday, the footer will stay as is until next Monday.


Gord