Increment cell number when printing

  • Thread starter Thread starter goody
  • Start date Start date
G

goody

I would like a particular cell in my spreadsheet to be incremented b
the value of one when I print the workbook. Any ideas on how I ca
achieve this.

Thanks
Goody
 
Hi
one way would be to use the workbook even 'Before_Print'. Put the
following code in your workbook module. It will increment cell A1 on
sheet1 each time you print:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
worksheets("Sheet1").Range("A1").value = _
worksheets("Sheet1").Range("A1").value + 1
End Sub
 
One way:

Put this in your ThisWorkbook code module (right-click on the workbook
title bar and select View Code):


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
Application.EnableEvents = False
Cancel = True
For Each wsSheet In ActiveWindow.SelectedSheets
wsSheet.PrintOut
Next wsSheet
With Sheets("Sheet1").Range("A1")
.Value = .Value + 1
End With
Application.EnableEvents = True
End Sub

Adjust your sheet and range reference to suit.
 
Note that Frank's interpreted your post to mean that you want to
increment the cell before you print, and I thought you wanted to
increment it after you print. From your post, I can't tell.

Before is obviously much simpler.
 
JE said:
Note that Frank's interpreted your post to mean that you want to
increment the cell before you print, and I thought you wanted to
increment it after you print. From your post, I can't tell.

Before is obviously much simpler.
Click to expand...

Hi JE
wasn't sure either so I took the more easier approach - after all it's
sunday afternoon <vbg>
Frank
 
I have inserted the code but the number doesn't seem to increment afte
printing (using the increment after printing suggestion).

What am I doing wrong?

Thanks
Goody
 
Hi
where have you put this code? It has to go in the workbook module and
not in a standard module. Try the following:
- open the VBA editor (ALT + F11)
- locate 'ThisWorkbook' in the left explorer treeview of you project
- double-click on this item
- paste the code in the appearing editor window
- close the VBA editor
- save the workbook and try again
 
I am still having difficulty implementing this. Is there any chanc
someone can email me a workbook with the coding already in it an
working?

email is (e-mail address removed)

Thanks
Goody
 
Back
Top