Free Microsoft Excel 2013 Quick Reference

Updating Headers/Footers in VB

Morning

I am updating a whole workbook of Headers and Footers using the following:

Public Sub UPDATE_HEADER_FOOTER()
Dim mysheet As Worksheet
For Each mysheet In ActiveWorkbook.Sheets
With mysheet.PageSetup
.RightFooter = "Issued by Central Personnel - July 28th 2006"
.RightHeader = "&""Arial,Bold""&11Reporting Period 4" & vbCr & _
"4 Weeks to 16 July 2006"

End With
Next
End Sub

However when the Macro hits a Sheet containing a Chart (Fill page) it stops and doesn't continue with the rest of the book. (i do want the charts to have a H and F as well).

Error Type mismatch (Error 13)

Please could anyone drop me a hint.?

thanks for your help.


Post your answer or comment

comments powered by Disqus
Hello there

This drives me mad.... how can I automatically repeat a header or footer
into each worksheet of an Excel workbook? Somebody please help!!

I currently go in and manually change each one, which is a real pain, when I
want exactly the same header/footer in each worksheet.

Any advice gratefully received! Thanks.

I create a header & footer in the first worksheet of the excel file. The
following worksheets in the same file have no header and footer. I did not
have this problem with my older version of Office.

Can someone explain why the character & will not appear in the header/footer in Excel.

For example, I need to insert the company's name which is Morrison & Foerster, LLP in the header.

The result is only Morrison Foerster LLP.

The '&' will not appear.

Can anyone explain why and recommend a solution.

Thank you and best regards,

Is there a way to copy header /footer information?

a. on multiple sheets in a workbook?
and
b. from one workbook to another.

I have to create monthly reports and only need to change the month, but the remainder of the text remains the same.

I am currently updating header/footer on a worksheet by worksheet basic

Thanks
Tri[

An option should exist to create a uniform header/footer format across
worksheets in Excel. I shouldn't have to create the same header/format
format across multiple sheets. I should be able to create one header format
and then just change the text/content across the sheets.

This is a big hassle when trying to create print-ready, multiple worksheet
files in Excel that have similar formatting.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Is there any way to completely delete system-generated headers or footers
that appear in the dropdown lists for custom headers and footers in Page
Setup? Specifically, company and author information that appears in the
dropdown lists from the file being generated on a specific machine should not
appear when the file is going to a different company. I cleared information
in the Properties and it made no difference.

Need help - if my header / footer contains special character "&" in excel -
the result come out incorrect.
Is there any way to put this special character as part of my header / footer
in excel ?

Hi,

How can I protect Headers and Footers in Excel, there doesn't seem to be an
option in eitehr protect sheet or workbook

Thanks

Can anyone tell me how can I add an image as header or footer in excel?

Thanks

Need help - if my header / footer contains special character "&" in excel -
the result come out incorrect.
Is there any way to put this special character as part of my header / footer
in excel ?

An option should exist to create a uniform header/footer format across
worksheets in Excel. I shouldn't have to create the same header/format
format across multiple sheets. I should be able to create one header format
and then just change the text/content across the sheets.

This is a big hassle when trying to create print-ready, multiple worksheet
files in Excel that have similar formatting.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

I am trying to format headers and footers in an Excel 2003 Spreadsheet using VBA.

More specifically I would like to justify the the text in the Centre and Right hand Headers/Footers to align to the left. When I use the &L operator it moves the text into the Left hand header/footer. I've tried padding with spaces to manually align text, but this has no effect no matter how many spaces I input to the text.

I've also tried putting all the text into the Left hand header/footer in the hope that it will stretch across the page, but this only gives me an error (the total text is less than 255 characters).

One final point - I would like to put an ampersand (&) in as text but Excel always reads this as an operator prefix even when I try adding it as Chr(38) rather than the actual & sign. Can this be got around?

Thanks for any help or advice.

a printing date and time, or a path in one sheet:

1. Select Page Layout -> Print Titles (in Page Setup Group) -> Header/Footer tab.
2. Select Custom Footer.
3. Select a section and click the Tab, File, Path, Time and Date commands, and then click OK.

or a path to all the sheets in a workbook:

1. Select a sheet tab, right click and select Select All Sheets.
2. Follow the steps above.
3. Select the sheet tab, right click and select Ungroup Sheets.

 To add a picture (such as a company logo) to the header/footer:
1. Select Page Layout -> Print Titles (in Page Setup Group) -> Header/Footer tab.
2. Select Custom Header.
3. Select a section and click the Insert Picture command (the second command from the right).
4. In the Insert Picture dialog box, search for and select the logo or picture you want to add.
5. To format the picture, click the Format Picture command (the first command from the right) and click OK.

the codes that allow to format header/footer is:

With ActiveSheet.PageSetup
.LeftHeader = "&""Times New Roman,Bold""&12&P"
End With

i want to replace - font name & size

the result after replacing with variables -
With ActiveSheet.PageSetup
.LeftHeader = "&" Fname " & Fsize &P"
End With
for some reason this line is wrong, what's wrong ???

I have an excel document with about 15 worksheets for which I want the same
headers & footers on each. Can I repeat them without have to set up
individually?

When you go to the headers/footers in Page Setup, there are a number of
selections that have been used before, or you can create Customer
Header/Footers. My question is, how do you clear all of the previously used
headers/footers? Is this data saved somewhere that can be deleted so as not
to identify personal information that is contained within the used
headers/footers?

Hi Guys

Please help me if you know how to lock header footer in excel 2007. And if you also know how to lock some certain ribbon control options so that form users cannot use them. For example, Insert - Text etc. And also locking . Quick Access Toolbar locking?

Many thanks
Heaven

Hello,

I am not skilled in VB, please be patient.

I am trying to use a cell reference (i.e. company abc) on sheet 1 of my file to be placed in the header/footer of sheets 2-6 of my file. At present, I have to manually change all of the headers and footers in these sheets.

Thanks

In the FIle -> Page Setup -> Header/Footer tab I would like to add and delete
saved headers and footers.
Excel 2003 SP2

I have a spreadsheet that contains both Sheets and Charts and I want to Reference the contents of a cell in the header/footer of both types using VB.

The following Micosoft web site contains a nice simple piece of code that I would like to use if possible - uses the Workbook_BeforePrint method to add the cell contents to the header/footer before printing :-

http://support.microsoft.com/kb/273028

The following VB code works when printing a Sheet but not with a Chart.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   ActiveSheet.PageSetup.LeftFooter = Sheet1.Range("a1").Value
End Sub
I can make the code work with a Chart as follows

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   ActiveChart.PageSetup.LeftFooter = Sheet1.Range("a1").Value
End Sub
My problem - if I combine the two I get an error because if the Sheet is active then the Chart is not, and the reverse of this, the Sheet/Chart that is not active generates the error in the code.

Private Sub
Workbook_BeforePrint(Cancel As Boolean)
   ActiveSheet.PageSetup.LeftFooter = Sheet1.Range("a1").Value
   ActiveChart.PageSetup.LeftFooter = Sheet1.Range("a1").Value
End Sub
The ideal solution would be for a simple modification to the above code but I seem to be stuck hence any help would be appreciated.

Robert

I want to format the date in dd/mmm/yyyy format in the header/footer of any worksheet. Any way out ?

Thanks in advance...

Srini

Hi,

How can I put in a different date format in the Header/Footer of the sheet for printing. Excel only appears to allow the dd-mm-yy format whereas I woulld like to use something different e.g. dd-Mmm-yy

I have the following code but the file name overlaps. How do I add an line so the file name goes on another line: Like:Path: C:/Documents and SettingsMy Documents new line then Accounting/May 2010?


	VB:
	
 InsertHeaderFooter() 
     ' inserts the same header/footer in all worksheets
    Dim ws As Worksheet 
    Application.ScreenUpdating = False 
    For Each ws In ActiveWorkbook.Worksheets 
        Application.StatusBar = "Changing header/footer in " & ws.Name 
        With ws.PageSetup 
             ' .LeftHeader = "Company name"
             ' .CenterHeader = "Page &P of &N"
            .RightHeader = "&D &T" 
             
            .LeftFooter = "Path : " & ActiveWorkbook.Path 
            .CenterFooter = "Workbook name &F" 
            .RightFooter = " Page &P of &N" 
        End With 
    Next ws 
    Set ws = Nothing 
    Application.StatusBar = False 
End Sub 
[B][/B] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines



No luck finding an answer? You could always try Google.