Header or Footer - Reference a Cell

[enna49]

Is it possible to reference a Cell in the Header or Footer. Maybe Code etc.
What I am trying to do is use the same worksheet every month. The date is
printed from the Header and want this to be picked up from data in the
worksheet. The Tabs are used for the Description in the Header.

Thanks
Anne

 

[OssieMac]

Try the following code. Not sure how much instruction you need but just in
case, here it is.

To insert the code

Alt/F11 to open the VBA editor
Double click ThisWorkbook in the project explorer on the left
Copy the code and paste it into the large white area of the editor

A space and underscore at the end of a line is a line break in an otherwise
single line of code.

Edit the code to insert your preferred header etc including the cell
containing the date.

Delete or comment out (single quote at start of line) the lines for the
headers/footers you do not want to change

Click the VBA close button (the X with red background top right)

Save the workbook. (If xl2007 then Save As a macro enabled workbook).

You can open the VBA editor again with Alt/F11 and get back to the code
again by double clicking ThisWorkbook.

Feel free to get back to me if you have problems. particularly in how to
concatenate your required header with the date

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'Control the worksheet to which code is applied
Select Case ActiveSheet.Name

Case "Sheet1"
With Sheets("Sheet1").PageSetup

'Adjust format in double quotes to suit.
'Chr(13) is a line feed.
.CenterHeader = "My Header " & Chr(13) & _
Format(Range("G2"), "dd mmm yyyy")

'delete the header/footer lines not required
'otherwise the null ("") will delete any
'already set up in you Page Setup.
.LeftHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With

Case "Sheet2"
'can have an many cases as you like
'with different settings to above
'no need to include sheets that do
'not require the header or footer
'to be adjusted.
End Select



End Sub

 

[fstwymike]

I have a similar situation - thought I could use your suggestion, but it's
not working.
Mine is for the right header.
Trying to put a 2 letter code (i.e. RS) on Line 1,
then have "Page x of y Pages" on Line 2,
where x = &[Page],
and y = Sheet2!V14 cell.

FYI - I can't use the shortcut &[Pages] because that references total pages,
not the number actually being printed. Also, can't change print area,
because it needs to be variable.

Your help is greatly appreciated by this novice!!!

 

[OssieMac]

Hi,

Set up macro ss per my previous post and use following code:

.RightHeader = "RS" & Chr(10) & "&P of " _
& Sheets("Sheet2").Range("V14")


Note: The space and underscore at end of first line is simple a line break
in an otherwise single line of code.

Just for interest, in the headers and footers you can use Chr(10) or Chr(13)
for a line feed.


--
Regards,

OssieMac

I have a similar situation - thought I could use your suggestion, but it's
not working.
Mine is for the right header.
Trying to put a 2 letter code (i.e. RS) on Line 1,
then have "Page x of y Pages" on Line 2,
where x = &[Page],
and y = Sheet2!V14 cell.

FYI - I can't use the shortcut &[Pages] because that references total pages,
not the number actually being printed. Also, can't change print area,
because it needs to be variable.

Your help is greatly appreciated by this novice!!!

Try the following code. Not sure how much instruction you need but just in
case, here it is.

To insert the code

Alt/F11 to open the VBA editor
Double click ThisWorkbook in the project explorer on the left
Copy the code and paste it into the large white area of the editor

A space and underscore at the end of a line is a line break in an otherwise
single line of code.

Edit the code to insert your preferred header etc including the cell
containing the date.

Delete or comment out (single quote at start of line) the lines for the
headers/footers you do not want to change

Click the VBA close button (the X with red background top right)

Save the workbook. (If xl2007 then Save As a macro enabled workbook).

You can open the VBA editor again with Alt/F11 and get back to the code
again by double clicking ThisWorkbook.

Feel free to get back to me if you have problems. particularly in how to
concatenate your required header with the date

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'Control the worksheet to which code is applied
Select Case ActiveSheet.Name

Case "Sheet1"
With Sheets("Sheet1").PageSetup

'Adjust format in double quotes to suit.
'Chr(13) is a line feed.
.CenterHeader = "My Header " & Chr(13) & _
Format(Range("G2"), "dd mmm yyyy")

'delete the header/footer lines not required
'otherwise the null ("") will delete any
'already set up in you Page Setup.
.LeftHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With

Case "Sheet2"
'can have an many cases as you like
'with different settings to above
'no need to include sheets that do
'not require the header or footer
'to be adjusted.
End Select



End Sub

 

[OssieMac]

Afterthought. Following inserts the words Page and Pages so it appears as:

RS
Page x of y Pages

..RightHeader = "RS" & Chr(13) & "Page &P of " _
& Sheets("Sheet2").Range("V14") & " Pages"
--
Regards,

OssieMac

Hi,

Set up macro ss per my previous post and use following code:

.RightHeader = "RS" & Chr(10) & "&P of " _
& Sheets("Sheet2").Range("V14")


Note: The space and underscore at end of first line is simple a line break
in an otherwise single line of code.

Just for interest, in the headers and footers you can use Chr(10) or Chr(13)
for a line feed.


--
Regards,

OssieMac

I have a similar situation - thought I could use your suggestion, but it's
not working.
Mine is for the right header.
Trying to put a 2 letter code (i.e. RS) on Line 1,
then have "Page x of y Pages" on Line 2,
where x = &[Page],
and y = Sheet2!V14 cell.

FYI - I can't use the shortcut &[Pages] because that references total pages,
not the number actually being printed. Also, can't change print area,
because it needs to be variable.

Your help is greatly appreciated by this novice!!!

Try the following code. Not sure how much instruction you need but just in
case, here it is.

To insert the code

Alt/F11 to open the VBA editor
Double click ThisWorkbook in the project explorer on the left
Copy the code and paste it into the large white area of the editor

A space and underscore at the end of a line is a line break in an otherwise
single line of code.

Edit the code to insert your preferred header etc including the cell
containing the date.

Delete or comment out (single quote at start of line) the lines for the
headers/footers you do not want to change

Click the VBA close button (the X with red background top right)

Save the workbook. (If xl2007 then Save As a macro enabled workbook).

You can open the VBA editor again with Alt/F11 and get back to the code
again by double clicking ThisWorkbook.

Feel free to get back to me if you have problems. particularly in how to
concatenate your required header with the date

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'Control the worksheet to which code is applied
Select Case ActiveSheet.Name

Case "Sheet1"
With Sheets("Sheet1").PageSetup

'Adjust format in double quotes to suit.
'Chr(13) is a line feed.
.CenterHeader = "My Header " & Chr(13) & _
Format(Range("G2"), "dd mmm yyyy")

'delete the header/footer lines not required
'otherwise the null ("") will delete any
'already set up in you Page Setup.
.LeftHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With

Case "Sheet2"
'can have an many cases as you like
'with different settings to above
'no need to include sheets that do
'not require the header or footer
'to be adjusted.
End Select



End Sub




--
Regards,

OssieMac


:

Is it possible to reference a Cell in the Header or Footer. Maybe Code etc.
What I am trying to do is use the same worksheet every month. The date is
printed from the Header and want this to be picked up from data in the
worksheet. The Tabs are used for the Description in the Header.

Thanks
Anne

 

[fstwymike]

It worked! THANKS so much!!!

Afterthought. Following inserts the words Page and Pages so it appears as:

RS
Page x of y Pages

.RightHeader = "RS" & Chr(13) & "Page &P of " _
& Sheets("Sheet2").Range("V14") & " Pages"
--
Regards,

OssieMac

Hi,

Set up macro ss per my previous post and use following code:

.RightHeader = "RS" & Chr(10) & "&P of " _
& Sheets("Sheet2").Range("V14")


Note: The space and underscore at end of first line is simple a line break
in an otherwise single line of code.

Just for interest, in the headers and footers you can use Chr(10) or Chr(13)
for a line feed.


--
Regards,

OssieMac

I have a similar situation - thought I could use your suggestion, but it's
not working.
Mine is for the right header.
Trying to put a 2 letter code (i.e. RS) on Line 1,
then have "Page x of y Pages" on Line 2,
where x = &[Page],
and y = Sheet2!V14 cell.

FYI - I can't use the shortcut &[Pages] because that references total pages,
not the number actually being printed. Also, can't change print area,
because it needs to be variable.

Your help is greatly appreciated by this novice!!!

:

Try the following code. Not sure how much instruction you need but just in
case, here it is.

To insert the code

Alt/F11 to open the VBA editor
Double click ThisWorkbook in the project explorer on the left
Copy the code and paste it into the large white area of the editor

A space and underscore at the end of a line is a line break in an otherwise
single line of code.

Edit the code to insert your preferred header etc including the cell
containing the date.

Delete or comment out (single quote at start of line) the lines for the
headers/footers you do not want to change

Click the VBA close button (the X with red background top right)

Save the workbook. (If xl2007 then Save As a macro enabled workbook).

You can open the VBA editor again with Alt/F11 and get back to the code
again by double clicking ThisWorkbook.

Feel free to get back to me if you have problems. particularly in how to
concatenate your required header with the date

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'Control the worksheet to which code is applied
Select Case ActiveSheet.Name

Case "Sheet1"
With Sheets("Sheet1").PageSetup

'Adjust format in double quotes to suit.
'Chr(13) is a line feed.
.CenterHeader = "My Header " & Chr(13) & _
Format(Range("G2"), "dd mmm yyyy")

'delete the header/footer lines not required
'otherwise the null ("") will delete any
'already set up in you Page Setup.
.LeftHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With

Case "Sheet2"
'can have an many cases as you like
'with different settings to above
'no need to include sheets that do
'not require the header or footer
'to be adjusted.
End Select



End Sub




--
Regards,

OssieMac


:

Is it possible to reference a Cell in the Header or Footer. Maybe Code etc.
What I am trying to do is use the same worksheet every month. The date is
printed from the Header and want this to be picked up from data in the
worksheet. The Tabs are used for the Description in the Header.

Thanks
Anne