Hi experts,

Is there a way to manipulate the footer in such a way that eac
successive page printed has a different alpha-numeric number in th
footer. This number would be related to the data on each page e.g. i
could be cell A20 for page1 , cell A40 for page 2, etc.

Thanks

Han

--
Hans4
-----------------------------------------------------------------------
Hans47's Profile: http://www.excelforum.com/member.php...fo&userid=1760
View this thread: http://www.excelforum.com/showthread.php?threadid=38441

Something like this?


Sub PrintMe()
Dim Cel As Range
Dim i As Long
Dim Sh As Worksheet
Dim Rng As Range
Dim Pages As Long

Pages = Int(Range(ActiveSheet.PageSetup.PrintArea).Rows.Co unt / 20)

For i = 1 To Pages
With ActiveSheet.PageSetup
.LeftFooter = Range("A" & 20 * i)
'simlar code for other footers
End With
ActiveWindow.SelectedSheets.PrintOut From:=i, To:=i, _
Copies:=1, Collate:=True
Next i
End Sub



"Hans47" wrote in
message ...

Hi experts,

Is there a way to manipulate the footer in such a way that each
successive page printed has a different alpha-numeric number in the
footer. This number would be related to the data on each page e.g. it
could be cell A20 for page1 , cell A40 for page 2, etc.

Thanks

Hans


--
Hans47
------------------------------------------------------------------------
Hans47's Profile:
http://www.excelforum.com/member.php...o&userid=17600
View this thread: http://www.excelforum.com/showthread...hreadid=384412

Thanks William,

Tried this with a button on Excel but came up with a VBA warning '400'
- no explanation. If I run the macro from VBA it tells me

method 'Range' of Object '_Global' failed

When I run it again it now says

application defined or object-defined error

What the...

I'm using Excel 2000-SP3.
Thanks again.


--
Hans47
------------------------------------------------------------------------
Hans47's Profile: http://www.excelforum.com/member.php...o&userid=17600
View this thread: http://www.excelforum.com/showthread...hreadid=384412

Hi again William,

Just another thing I tried..

the problem appears to be in the line calculating 'Pages' - it results
in a zero
which then causes the error.

Hans


--
Hans47
------------------------------------------------------------------------
Hans47's Profile: http://www.excelforum.com/member.php...o&userid=17600
View this thread: http://www.excelforum.com/showthread...hreadid=384412

You have not assigned the PrintArea?

Just a guess ...

"Hans47" wrote in
message ...

Hi again William,

Just another thing I tried..

the problem appears to be in the line calculating 'Pages' - it results
in a zero
which then causes the error.

Hans


--
Hans47
------------------------------------------------------------------------
Hans47's Profile:
http://www.excelforum.com/member.php...o&userid=17600
View this thread: http://www.excelforum.com/showthread...hreadid=384412

Hi William,

Thanks! that works.

Hans


--
Hans47
------------------------------------------------------------------------
Hans47's Profile: http://www.excelforum.com/member.php...o&userid=17600
View this thread: http://www.excelforum.com/showthread...hreadid=384412

It looks like you are trying to determine the number of printed pages. This
may help.

pgcnt = ExecuteExcel4Macro("Get.Document(50)")

This is from:

http://j-walk.com/ss/excel/tips/tip65.htm

"William Benson" wrote:

You have not assigned the PrintArea?

Just a guess ...

"Hans47" wrote in
message ...

Hi again William,

Just another thing I tried..

the problem appears to be in the line calculating 'Pages' - it results
in a zero
which then causes the error.

Hans


--
Hans47
------------------------------------------------------------------------
Hans47's Profile:
http://www.excelforum.com/member.php...o&userid=17600
View this thread: http://www.excelforum.com/showthread...hreadid=384412

Thanks JMB, that works well.

Another question: is it possible to do this using the
Workbook_BeforePrint

command - so that I don't have to create a new print window to

print a selection of pages (or only one page) and also maintain the
Excel print

preview.


--
Hans47
------------------------------------------------------------------------
Hans47's Profile: http://www.excelforum.com/member.php...o&userid=17600
View this thread: http://www.excelforum.com/showthread...hreadid=384412

Thanks - exactly what was missing in my bag of tricks, thanks for helping
both the OP and ME TOO JMB!!

"JMB" wrote in message
...
It looks like you are trying to determine the number of printed pages.
This
may help.

pgcnt = ExecuteExcel4Macro("Get.Document(50)")

This is from:

http://j-walk.com/ss/excel/tips/tip65.htm

"William Benson" wrote:

You have not assigned the PrintArea?

Just a guess ...

"Hans47" wrote in
message ...

Hi again William,

Just another thing I tried..

the problem appears to be in the line calculating 'Pages' - it results
in a zero
which then causes the error.

Hans


--
Hans47
------------------------------------------------------------------------
Hans47's Profile:
http://www.excelforum.com/member.php...o&userid=17600
View this thread:
http://www.excelforum.com/showthread...hreadid=384412