Page X of Y in Worksheet

[G.Hott]

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?

 

[ruic]

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.

 

[Barb Reinhardt]

Did you try putting this in the custom footer

Page &[Page] of &[Pages]

HTH,
Barb Reinhardt

 

[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

Did you try putting this in the custom footer

Page &[Page] of &[Pages]

HTH,
Barb Reinhardt

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?

 

[PaulD]

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


: 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

 

[TomPl]

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]

This isn't working. The cell i want the sheet 'x' of 'y' to print in is D11
so i changed the range to (D1111) 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]

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


:
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.