automatically roll the page number after you print

P

Perplexed!

I am trying to create a form that will automatically change to the next
number in the sequence after a copy is printed. Is this possible in excel
and if so how is it done?
 
J

Joerg

Not exactly what you need, but maybe a start: You could use Excel's
BeforePrint event. Put following code into the code page of ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.Range("A1") = ActiveSheet.Range("A1") + 1
End Sub

This would increase the value in A1 every time you are about to print.
Drawbacks: Changes value before print, not after. Changes the value also if
PrintPreview is used or printing is cancelled.

Cheers,

Joerg
 
P

Perplexed!

Thanks Joerg,

I think that this will work, hwoever I'm having a little trouble getting it
to work, I went into VBE and inserted a module, cut and pasted what you wrote
then put in my starting number in cell A1 and went to the print preview
screen and nothing happened. I did manage to get it to work once at my
office, but when I tried it again at home it wouldn't work. I followed the
steps to the letter on the office help site on Creating my own worksheet
function but I must have done something different when it worked(obviously!)
and I cannot for the life of me remember what I did! If you could let me
know what step I am missing it would mean my undying gratitude! Thanks in
advance.
 
G

Gord Dibben

Code goes into the Thisworkbook module, not a standard module that you created
with Insert>Module.

Select your workbook/project.

Expand the "Microsoft Excel Objects"

Double-click on Thisworkbook and paste the code in there.


Gord Dibben MS Excel MVP
 
Top