Changing data in footer in VBA

[Hans47]

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

[William Benson]

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.Count / 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Hans47]

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]

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

[William Benson]

You have not assigned the PrintArea?

Just a guess ...

"Hans47" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Hans47]

Hi William,

Thanks! that works.

Hans

[JMB]

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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>
>

[Hans47]

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.

[William Benson]

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

"JMB" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>>