Automating populating headers on multiple worksheets

eshie

New Member
Joined
Feb 12, 2004
Messages
4
In Excel 2000, I have a workbook with numerous worksheets. I want to use the same header & footer information on each. How can I do this once and populate all worksheets with this info instead of having to do for each one?

Help!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Non-VBA:

Right click on a sheet tab, left click on Select All Sheets, then from the menu bar select File > Page Setup > HeaderFooter tab, and enter your text.



VBA:

Sub Macro1()
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In Worksheets
ws.PageSetup.CenterHeader = "This is my center header text"
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi,
Thanks for the quick response. I want to use VBA. Is there a way I could use an input box to capture the header info (2 lines of data with a carriage return between each line) and pass this to the ws.PageSetup.CenterHeader variable? Same question applies to the footer (I will have 3 lines in the left footer section, 1 line in the center footer section and 1 line in the footer right section). I appreciate your help! :p
 
Upvote 0
I don’t thing you can use an Inputbox because you want multiple lines for your headers/footers. However, you could use a Form e.g. (exhibit limited to the header and left footer):
Headers and Footers - using a Form.xls
ABCDEFGHIJ
1
2
3Header
4Line1
5
6
7Line2
8
9
10LeftFooter
11Line1
12
13
14Line2
15
16
17Line3
18
19
20
21GoClose
22
23
24
25
Sheet2


So, prepare the Form with Textboxes and labels, and 2 buttons.

Post back if you need help with doing a Form.

Form module:

Code:
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim msg1 As String
Dim msg2 As String
Dim msg3 As String
Dim msg4 As String
Dim msg5 As String
Dim msg6 As String
Dim msg7 As String

Application.ScreenUpdating = False

msg1 = TextBox1.Value    'Center header - line 1
msg2 = TextBox2.Value    'Center header - line 2
msg3 = TextBox3.Value    'Left footer - line 1
msg4 = TextBox4.Value    'Left footer - line 2
msg5 = TextBox5.Value    'Left footer - line 3
msg6 = TextBox6.Value    'Center footer
msg7 = TextBox7.Value    'Right footer

For Each ws In Worksheets
With ws.PageSetup
     .CenterHeader = msg1 & vbLf & msg2
     .LeftFooter = msg3 & vbLf & msg4 & vbLf & msg5
     .CenterFooter  = msg6
     .RightFooter = msg7
 End With
Next

Application.ScreenUpdating = True
End Sub


Private Sub CommandButton2_Click()
End
End Sub
Standard module:
Code:
Sub myForm()
UserForm1.Show
End Sub

Notes

1. Formatting:

If you want the Center Footer to be say Arial font, bold, 10 pt and uppercase, then replace this line:
.CenterFooter = msg6

with:
.CenterFooter = "&""Arial,Bold""&10" & UCase(msg6)

For a list of VBA formatting codes, see the VBA help file – in the Search box, type in: “Formatting Codes for Headers and Footers” (no quotes).

2. Hard coding VBA formatting codes

It is likely that you do not want users to change certain footers e.g. the right footer must always show say the date or show “Page 1 of whatever”. If that is the case, then remove Textbox7 from the Form, and change the macro to the following:

With ws.PageSetup
.CenterHeader = msg1 & vbLf & msg2
.LeftFooter = msg3 & vbLf & msg4 & vbLf & msg5
.CenterFooter = msg6
.RightFooter = "Page &P of &N"
End With

Or, something like:
.RightFooter = Format(Now(), "dd-mmm-yy")

Alternatively, leave Textbox7 on the Form, but add this code to the Form module:
Code:
Private Sub UserForm_Initialize()
   TextBox7.Text = "Format(Now(), ""dd-mmm-yy"")"
End Sub
When you open the Form, Textbox7 is already filled out (color fill Textbox7 say pale red and tell your users not to touch the red text boxes).

HTH

Mike
 
Upvote 0
I have a standard Header and Footer that I use on all my office documents. Is there a way to APPLY this standard Header and Footer to an existing worksheet.

For example, when I do a query in another program and download the data into excel, I then want to APPLY my standard H & F. Does anyone know how to do this?

Thx,
DiMo
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top