Need to print path & file on ALL excel worksheets...

B

Buzzerd

Hey all,
Thanks in advance for the knowledge. I need to have as a default the
path and file name on all our excel spreadsheets to be printed on the
bottom of each page. I know you can do individual sheets with the
footer/header but I need this for all. Can it be done? If so, how?
Thanks
Buzz
 
M

Marvin P. Winterbottom

select all the worksheets by clicking on the first one then Shift-clicking on
the last one, then do your page setup and it will apply to all sheets
 
G

Gord Dibben

Buzz

To get path and name on each sheet of an existing workbook you can select all
sheets and set up your header/footer on the active sheet.

All sheets selected will get same set up.

To have it as default on all new workbooks, you can set up a new workbook as you
wish then save as>filetype>excel template)*.xla)

Name it BOOK and store it in your XLSTART folder.

This will be the template for all new workbooks when you click on File>New(not
file>new...) or hit the New Icon

You can also save a new workbook with one sheet in it with your header/footer
set up.

Save As a template. Name it SHEET and store in your XLSTART folder.

This will the default Insert>New Worksheet.

You can also use a macro to set all sheets header/footer

Sub Path_All_Sheets()
Set wkbktodo = ActiveWorkbook
For Each ws In wkbktodo.Worksheets
ws.PageSetup.RightFooter = ActiveWorkbook.FullName & " " & Chr(13) _
& Application.UserName & " " & Date
Next
End Sub

Strip out the parts you don't want.


Gord Dibben MS Excel MVP
 
G

Gord Dibben

Buzz

You obviously did not look at all the replies you got.

To get path and name on each sheet of an existing workbook you can select all
sheets and set up your header/footer on the active sheet.

All sheets selected will get same set up.

To have it as default on all new workbooks, you can set up a new workbook as you
wish then save as>filetype>excel template)*.xla)

Name it BOOK and store it in your XLSTART folder.

This will be the template for all new workbooks when you click on File>New(not
file>new...) or hit the New Icon

You can also save a new workbook with one sheet in it with your header/footer
set up.

Save As a template. Name it SHEET and store in your XLSTART folder.

This will the default Insert>New Worksheet.

You can also use a macro to set all sheets header/footer

Sub Path_All_Sheets()
Set wkbktodo = ActiveWorkbook
For Each ws In wkbktodo.Worksheets
ws.PageSetup.RightFooter = ActiveWorkbook.FullName & " " & Chr(13) _
& Application.UserName & " " & Date
Next
End Sub

Strip out the parts you don't want.


Gord Dibben MS Excel MVP

Hey all,
Thanks in advance for all the knowledge. Ok I followed the
instructions on:
http://www.ozgrid.com/VBA/WorkbookPath.htm but that only gives me:
[Path]File1(not actually the file name!). I want the drive c:, d: or
L: then folder then file. Is this possible.
Thanks again.
Buzz
Click to expand...

Gord Dibben MS Excel MVP
 
B

Buzzerd

Help,
Thanks in advance for all the help. I followed the instruction but
when I get to the part of - Now go to Window, select Personal.xls and
"Hide" - there is no where to select Personal.xls. I did this a few
times but still the same thing. What am I doing wrong? I'll tool
around a bit to see if I can find it but as so far I'm lost.
Thanks
Buzz
 
G

Gord Dibben

Buzz

You created the Personal.xls?

You copied the Path_All_Sheets macro into a module in Personal.xls?

You hit ALT + Q to return to the Excel window?

When you click on Window no Personal.xls is listed?

Perhaps it is already hidden.

Try Window>Unhide. If listed there, don't unhide it, just leave hidden.


Gord
 
B

Buzzerd

I tried all you wrote and saved the personal.xls file. When I click
window the unhide button is not clickable and personal.xls is not to be
seen. I must be missing a step somewhere, I'll go back again and see.
Buzz
 
B

Buzzerd

Ok so I still have a problem when I click window and don't see th
personal.xls file but I'm curious about something, when this is al
said and done everytime I turn on excel from here on I will see th
path and file on each printed page WITHOUT having to go into the macr
file? If I have to go into that each time that nullifies everything.
Please let me know. I want my workers to just go into excel and prin
and have that come up ALL the time.
Thanks again.
Buz
 
G

Gord Dibben

Buzz

Don't know where your Personal.xls got to.

Check in the folder at C:\Documents and Settings\username\Application
Data\Microsoft\Excel\XLSTART

If it is not there, you have messed up somewhere.

As an alternative go to John Walkenbach's site and download the add-in
ADDPATH.XLA

http://www.j-walk.com/ss/excel/files/addpath.htm

Distribute this add-in to your workers or place it in a folder that is available
to all if on a network.

You will still have to run it once for each workbook untill all have the code
established.


Gord
 
Top