Can contents of a cell be used in header/footer?

[Anon Ymous]

All,

I am interested in making a custom header that contains text from a certain
cell. There are standard fields that can be added to a header or footer
using the standard buttons in the header or footer dialog box.

These are:
&[Page], &[Pages], &[Date], &[Time], &[Path]&[File], &[File], &[Tab]

I was wondering if I could create a custom field for my header that would
insert the contents of a certain cell.

For instance, cell A7 contains a Vendor name, let's say "XYZ Supply". I
would like to create a header that says, "Vendor: XYZ Supply" by creating a
custom header something like: Vendor: &[Cell A7]. I've tried a number of
configurations for this custom field, but haven't found a way to do it yet.

Does anyone know how to do it?

Thanks,

Scott
(e-mail address removed)

 

[Frank Kabel]

Hi
not possible without using VBA. Put the following code in your workbook
module (not in a standard
module):
Private Sub Workbook BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In me.Worksheets
With wkSht.PageSetup
.CenterFooter = wks.range("A1").value
End With
Next wkSht
End Sub

This will insert cell A1 of each sheet in the footer

 

[JE McGimpsey]

One way:

Put this in the ThisWorbook code module (right-click on the workbook
title bar and choose View Code:


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
For Each wsSheet In ActiveWindow.SelectedSheets
With wsSheet
If .Name = "Sheet1" Then _
.PageSetup.LeftHeader = "Vendor: " & _
.Range("A7").Text
Next wsSheet
End Sub

Change "Sheet1" to the name of your sheet.

 

[Anon Ymous]

Greetings,

This code works if you put an "End With" line after the ".Range" line.
I was able to put the cell contents in the RightHeader and place my leading
text (PO for vender in the Center Header.
I was hoping to define the cell as a variable that I could dynamically
include in my header text so it could be all concatinated together in one
location, but this will do for now.

Thanks!!

One way:

Put this in the ThisWorbook code module (right-click on the workbook
title bar and choose View Code:


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
For Each wsSheet In ActiveWindow.SelectedSheets
With wsSheet
If .Name = "Sheet1" Then _
.PageSetup.LeftHeader = "Vendor: " & _
.Range("A7").Text
Next wsSheet
End Sub

Change "Sheet1" to the name of your sheet.

All,

I am interested in making a custom header that contains text from a certain
cell. There are standard fields that can be added to a header or footer
using the standard buttons in the header or footer dialog box.

These are:
&[Page], &[Pages], &[Date], &[Time], &[Path]&[File], &[File], &[Tab]

I was wondering if I could create a custom field for my header that would
insert the contents of a certain cell.

For instance, cell A7 contains a Vendor name, let's say "XYZ Supply". I
would like to create a header that says, "Vendor: XYZ Supply" by creating a
custom header something like: Vendor: &[Cell A7]. I've tried a number of
configurations for this custom field, but haven't found a way to do it yet.

Does anyone know how to do it?

Thanks,

Scott
(e-mail address removed)

 

[NickMinUK]

Not as sophisticated as these other guys, so I use ASAP Utilities t
change sheet tab to cell content eg A1, then simply use header setting
to select sheet tab in left, centre or right. The cell contents of A
can of course be concatenated data from any number of cells in eac
sheet

 

[Dave Peterson]

Maybe you could use a range name on that worksheet and point at a different cell
when you want a different location:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
For Each wsSheet In ActiveWindow.SelectedSheets
With wsSheet
If .Name = "Sheet1" Then _
.PageSetup.LeftHeader = "Vendor: " & _
.Range("VendorName").Text
End With
Next wsSheet
End Sub

Use Insert|Name|Define to create the name and to change to a different location.

All,

I am interested in making a custom header that contains text from a certain
cell. There are standard fields that can be added to a header or footer
using the standard buttons in the header or footer dialog box.

These are:
&[Page], &[Pages], &[Date], &[Time], &[Path]&[File], &[File], &[Tab]

I was wondering if I could create a custom field for my header that would
insert the contents of a certain cell.

For instance, cell A7 contains a Vendor name, let's say "XYZ Supply". I
would like to create a header that says, "Vendor: XYZ Supply" by creating a
custom header something like: Vendor: &[Cell A7]. I've tried a number of
configurations for this custom field, but haven't found a way to do it yet.

Does anyone know how to do it?

Thanks,

Scott
(e-mail address removed)

 

[Dave Peterson]

Typo alert:

..CenterFooter = wks.range("A1").value
should be:
..CenterFooter = wksht.range("A1").value

Hi
not possible without using VBA. Put the following code in your workbook
module (not in a standard
module):
Private Sub Workbook BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In me.Worksheets
With wkSht.PageSetup
.CenterFooter = wks.range("A1").value
End With
Next wkSht
End Sub

This will insert cell A1 of each sheet in the footer

--
Regards
Frank Kabel
Frankfurt, Germany

All,

I am interested in making a custom header that contains text from a
certain cell. There are standard fields that can be added to a
header or footer using the standard buttons in the header or footer
dialog box.

These are:
&[Page], &[Pages], &[Date], &[Time], &[Path]&[File], &[File], &[Tab]

I was wondering if I could create a custom field for my header that
would insert the contents of a certain cell.

For instance, cell A7 contains a Vendor name, let's say "XYZ Supply".
I would like to create a header that says, "Vendor: XYZ Supply" by
creating a custom header something like: Vendor: &[Cell A7]. I've
tried a number of configurations for this custom field, but haven't
found a way to do it yet.

Does anyone know how to do it?

Thanks,

Scott
(e-mail address removed)