Changing data in footer in VBA

[Hans47]

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

 

[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]

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]

Hi William,

Thanks! that works.

Hans

 

[Guest]

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

 

[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!!