Good morning! I'm really hoping someone can assist me.
I have a worksheet with a title block in rows 1-13. This title block repeats
on each sheet. I would like for a cell to show when printed "Sheet 'x' of
'y'". Is this possible?

--
G. Hott

Try something like this:

I = 1;
For Each WkSheet in Application.ActiveWorkbook.Windows(1)
Range("A1:A1).Value = I
I = I + 1
Next

What is does is it goes through the worksheets in the current workbook that
is selected. Just in case you have more than one open workbook, and then
puts the value of I in cell A1. But you can have put whatever you want in
cell A1 or any other cell by changing the Range value.

--
Rui


"G.Hott" wrote in message
...
Good morning! I'm really hoping someone can assist me.
I have a worksheet with a title block in rows 1-13. This title block
repeats
on each sheet. I would like for a cell to show when printed "Sheet 'x' of
'y'". Is this possible?

--
G. Hott

Did you try putting this in the custom footer

Page &[Page] of &[Pages]

HTH,
Barb Reinhardt



"G.Hott" wrote:

Good morning! I'm really hoping someone can assist me.
I have a worksheet with a title block in rows 1-13. This title block repeats
on each sheet. I would like for a cell to show when printed "Sheet 'x' of
'y'". Is this possible?

--
G. Hott

Thanks, Barb, but this will not work. i cannot use a header or footer in this
application. the only form of a header i can have are the repeating 13 rows
at the top of each sheet. I would like the Sheet 'x' of''y' to be in one of
these cells.
--
G. Hott


"Barb Reinhardt" wrote:

Did you try putting this in the custom footer

Page &[Page] of &[Pages]

HTH,
Barb Reinhardt



"G.Hott" wrote:

Good morning! I'm really hoping someone can assist me.
I have a worksheet with a title block in rows 1-13. This title block repeats
on each sheet. I would like for a cell to show when printed "Sheet 'x' of
'y'". Is this possible?

--
G. Hott

Put this code in the Thisworkbook object, this will update the text each
time you print the file. Replace [A1] with the cell address you want to use

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim X As Integer, Y As Integer

Y = ThisWorkbook.Sheets.Count
For X = 1 To Y
Sheets(X).[A1].Value = "Sheet " & X & " of " & Y
Next X

End Sub

Paul D


"G.Hott" wrote in message
...
: Good morning! I'm really hoping someone can assist me.
: I have a worksheet with a title block in rows 1-13. This title block
repeats
: on each sheet. I would like for a cell to show when printed "Sheet 'x' of
: 'y'". Is this possible?
:
: --
: G. Hott

I am guessing that you mean the title repeats on each printed page from one
single worksheet.
I am sceptical about doing this.
This is not dynamic.
But since you asked and I have been pondering this, the following code will
put "Page x of xx" in column "A" one line above each

horizontal page break. I have not tested it thoroughly, but it seems to
work. It is slow.

Sub AddPageNumbers()

Dim rngBreakLocation As Range
Dim intCounter As Integer

ActiveWindow.View = xlPageBreakPreview

For intCounter = 1 To ActiveSheet.HPageBreaks.Count

Set rngBreakLocation = ActiveSheet.HPageBreaks(intCounter).Location

rngBreakLocation.Offset(-1, 0).Value = "Page " _
& intCounter & " of " & ActiveSheet.HPageBreaks.Count

Next intCounter

ActiveWindow.View = xlNormalView

End Sub

Good Luck,

Tom

"G.Hott" wrote:

Good morning! I'm really hoping someone can assist me.
I have a worksheet with a title block in rows 1-13. This title block repeats
on each sheet. I would like for a cell to show when printed "Sheet 'x' of
'y'". Is this possible?

--
G. Hott

This isn't working. The cell i want the sheet 'x' of 'y' to print in is D11
so i changed the range to (D11:D11) is that correct?
I have two worksheets in the workbook but there is no reason to print from
the other sheet - however, it is essential to keep.
I'm putting this in the thisworkbook object - is that correct?
--
G. Hott


"ruic" wrote:

Try something like this:

I = 1;
For Each WkSheet in Application.ActiveWorkbook.Windows(1)
Range("A1:A1).Value = I
I = I + 1
Next

What is does is it goes through the worksheets in the current workbook that
is selected. Just in case you have more than one open workbook, and then
puts the value of I in cell A1. But you can have put whatever you want in
cell A1 or any other cell by changing the Range value.

--
Rui


"G.Hott" wrote in message
...
Good morning! I'm really hoping someone can assist me.
I have a worksheet with a title block in rows 1-13. This title block
repeats
on each sheet. I would like for a cell to show when printed "Sheet 'x' of
'y'". Is this possible?

--
G. Hott

Thanks - this seems to be the closest.
--
G. Hott


"ruic" wrote:
This is very close. However it's giving me the # of worksheets in the book.
What i need is the number of pages in the sheet. - Basically what you would
normally get by inserting page 'x' of 'y' in the header.
Thanks for helping me out.

Try something like this:

I = 1;
For Each WkSheet in Application.ActiveWorkbook.Windows(1)
Range("A1:A1).Value = I
I = I + 1
Next

What is does is it goes through the worksheets in the current workbook that
is selected. Just in case you have more than one open workbook, and then
puts the value of I in cell A1. But you can have put whatever you want in
cell A1 or any other cell by changing the Range value.

--
Rui


"G.Hott" wrote in message
...
Good morning! I'm really hoping someone can assist me.
I have a worksheet with a title block in rows 1-13. This title block
repeats
on each sheet. I would like for a cell to show when printed "Sheet 'x' of
'y'". Is this possible?

--
G. Hott