How do i save a custom footer in the excel drop down menu?

My standard practice is to set up a footer with "&[file]" in the left custom
footer box, "Page &[Page]/&[Pages]" in the center box and "&[Date]" in the
right box. I would like to save this in the drop down menu but have not been
able to find out how to do this. There are other footers there and I have no
idea where they came from. Any help? Thanks.

I set up two template workbooks with what I want each page layout to be.

The first one is named book.xlt (file|saveas|Template (*.xlt)).

This is stored in my XLStart folder. When I click the New icon on the standard
toolbar, I get a new workbook based on this template.

The second one is named sheet.xlt and is saved in the same location (XLStart
folder).

When I add a new worksheet to an existing workbook, I get a worksheet based on
this template.

(I really created a single sheet workbook and saved it as book.xlt, then I just
copied that book.xlt to sheet.xlt--all within my XLStart folder.)

JohnJ wrote:
>
> My standard practice is to set up a footer with "&[file]" in the left custom
> footer box, "Page &[Page]/&[Pages]" in the center box and "&[Date]" in the
> right box. I would like to save this in the drop down menu but have not been
> able to find out how to do this. There are other footers there and I have no
> idea where they came from. Any help? Thanks.

--

Dave Peterson

John

Custom footers are saved with the workbook and would be available in the drop
down for that workbook only.

I assume you wish this custom footer to be available for all workbooks and
sheets.

You could create a Book.xlt template to base all new workbooks upon.

You could also create a Sheet.xlt to base all new worksheets on.

To have all sheets contain the same footer, you can group the sheets and do
your footer setup on all sheets at once.

OR you could use a macro to set all sheets to the same footer in existing
workbooks and all new workbooks. Store the macro in your Personal.xls or in
an add-in.

Sub Set_All_Sheets()
Dim wkbktodo As Workbook
Dim ws As Worksheet
Set wkbktodo = ActiveWorkbook
For Each ws In wkbktodo.Worksheets
With ws.PageSetup
.LeftFooter = "&F"
.CenterFooter = """Page &P/&N"""
.RightFooter = "&D"
End With
Next ws
End Sub


Gord Dibben Excel MVP



On Tue, 20 Sep 2005 09:17:03 -0700, "JohnJ" <[email protected]>
wrote:

>My standard practice is to set up a footer with "&[file]" in the left custom
>footer box, "Page &[Page]/&[Pages]" in the center box and "&[Date]" in the
>right box. I would like to save this in the drop down menu but have not been
>able to find out how to do this. There are other footers there and I have no
>idea where they came from. Any help? Thanks.

These answers appear to work for new spreadsheets but I also would like to
add the footer to spreadsheets that I have received from other sources like
email or files from colleagues, hence the desire to have it included in the
footer drop down menu so that it could be added simply during printing setup.
I appreciate the answers received but I am uncertain how they could be used
without merging spreadsheets or other manipulation. Am I wrong about this?

John

"Gord Dibben" wrote:

> John
>
> Custom footers are saved with the workbook and would be available in the drop
> down for that workbook only.
>
> I assume you wish this custom footer to be available for all workbooks and
> sheets.
>
> You could create a Book.xlt template to base all new workbooks upon.
>
> You could also create a Sheet.xlt to base all new worksheets on.
>
> To have all sheets contain the same footer, you can group the sheets and do
> your footer setup on all sheets at once.
>
> OR you could use a macro to set all sheets to the same footer in existing
> workbooks and all new workbooks. Store the macro in your Personal.xls or in
> an add-in.
>
> Sub Set_All_Sheets()
> Dim wkbktodo As Workbook
> Dim ws As Worksheet
> Set wkbktodo = ActiveWorkbook
> For Each ws In wkbktodo.Worksheets
> With ws.PageSetup
> .LeftFooter = "&F"
> .CenterFooter = """Page &P/&N"""
> .RightFooter = "&D"
> End With
> Next ws
> End Sub
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 20 Sep 2005 09:17:03 -0700, "JohnJ" <[email protected]>
> wrote:
>
> >My standard practice is to set up a footer with "&[file]" in the left custom
> >footer box, "Page &[Page]/&[Pages]" in the center box and "&[Date]" in the
> >right box. I would like to save this in the drop down menu but have not been
> >able to find out how to do this. There are other footers there and I have no
> >idea where they came from. Any help? Thanks.
>
>

John

Use the Set_All_Sheets macro on all workbooks, new and existing.

File>New>Blank Workbook.

Hit ALT + F11 to open the Visual Basic Editor.

CTRL + r to get into Project Explorer window.

Right-click on your project/workbook and Insert>Module.

Copy/paste the macro into that module.

ALT + Q to return to Excel window.

File>Save As>File Type> Excel Add-in(*.xla)

Give it a name such as MyMacros, navigate to your Office\Library folder and
save it there.

Tools>Add-ins. Find your MyMacros and checkmark it.

Stick a button on your toolbar(Tools>Customize>Commands>Macros) and assign the
macro to that button.

Will work for all workbooks, new or old.


Gord Dibben Excel MVP



On Tue, 20 Sep 2005 11:33:02 -0700, "JohnJ" <[email protected]>
wrote:

>These answers appear to work for new spreadsheets but I also would like to
>add the footer to spreadsheets that I have received from other sources like
>email or files from colleagues, hence the desire to have it included in the
>footer drop down menu so that it could be added simply during printing setup.
> I appreciate the answers received but I am uncertain how they could be used
>without merging spreadsheets or other manipulation. Am I wrong about this?
>
>John
>
>"Gord Dibben" wrote:
>
>> John
>>
>> Custom footers are saved with the workbook and would be available in the drop
>> down for that workbook only.
>>
>> I assume you wish this custom footer to be available for all workbooks and
>> sheets.
>>
>> You could create a Book.xlt template to base all new workbooks upon.
>>
>> You could also create a Sheet.xlt to base all new worksheets on.
>>
>> To have all sheets contain the same footer, you can group the sheets and do
>> your footer setup on all sheets at once.
>>
>> OR you could use a macro to set all sheets to the same footer in existing
>> workbooks and all new workbooks. Store the macro in your Personal.xls or in
>> an add-in.
>>
>> Sub Set_All_Sheets()
>> Dim wkbktodo As Workbook
>> Dim ws As Worksheet
>> Set wkbktodo = ActiveWorkbook
>> For Each ws In wkbktodo.Worksheets
>> With ws.PageSetup
>> .LeftFooter = "&F"
>> .CenterFooter = """Page &P/&N"""
>> .RightFooter = "&D"
>> End With
>> Next ws
>> End Sub
>>
>>
>> Gord Dibben Excel MVP
>>
>>
>>
>> On Tue, 20 Sep 2005 09:17:03 -0700, "JohnJ" <[email protected]>
>> wrote:
>>
>> >My standard practice is to set up a footer with "&[file]" in the left custom
>> >footer box, "Page &[Page]/&[Pages]" in the center box and "&[Date]" in the
>> >right box. I would like to save this in the drop down menu but have not been
>> >able to find out how to do this. There are other footers there and I have no
>> >idea where they came from. Any help? Thanks.
>>
>>