Increment a number in a cell for each page printed

[Steve Vincent]

Hello,

I'm trying to print sequential numbers in a specific place on a spreadsheet
(not page numbers), which will increment each time the page prints. For
instance, the first page would look like this:

101 101 101
101 101 101

The next printed page would look like this:

102 102 102
102 102 102

I have read some posts and their answers' related links, but they don't
quite answer my need.

Any ideas where to start?
Thank you in advance,
Steve Vincent
(e-mail address removed)

 

[CLR]

Sounds like a job for a macro.........do Tools > Macro > RecordNewMacro and
go through all the steps to perform the actions you want by hand, and finish
by doing Tools > Macro > Stop recording............the next time you want to
do the same thing just run the macro.........

Vaya con Dios,
Chuck, CABGx3

 

[Mallycat]

Any ideas where to start?
Thank you in advance,
Steve Vincent
(e-mail address removed)

Steve

Create a new module in VBA and paste this macro

Sub PrintAndAdd()
Dim myRange As Range
Set myRange = Range("a1:c2")
For Each cell In myRange
cell.Value = cell.Value + 1
Next
ActiveWindow.SelectedSheets.PrintOut
End Sub


enter 1 1 1
1 1 1

Into cells a1:c2 you can obviously change the cells it points to

Create a button on your spreadsheet and link the macro to it.

Here is the spreadsheet anyway
http://members.optusnet.com.au/~allington65/Files/AddThenPrint.xls

Matt

 

[JLatham]

I'm thinking that if this were tied to the workbook's BeforePrint event, then
you don't even have to bother the user with choosing and running a macro.
Like most things in Excel it depends on a few things remaining static. In
this case the thing would be the worksheet name. Mallycat, I took the
liberty of adding your code inside of a sample here:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim AnySheet As Worksheet
Dim myRange As Range
Dim CellRange As Object

For Each AnySheet In ActiveWindow.SelectedSheets
'change "Sheet1" to actual sheet name
If AnySheet.Name = "Sheet1" Then
Set myRange = AnySheet.Range("a1:c2")
For Each CellRange In myRange
CellRange.Value = CellRange.Value + 1
Next
End If
Next
'delete the next line to
'actually let it print out
'this is here just for testing
Cancel = True
End Sub

Naturally, change the sheet name and cell range to fit your needs and once
you're sure it's working, remove the Cancel=True line to actually start
sending stuff to the printer.

The 'advantage' here is that whether you choose the one sheet or a group of
sheets, the update of the values on that sheet will be made without any extra
work on the part of the user.

 

[Steve Vincent]

Dear MallyCat and JLatham, thank you so much for your replies! MallyCat's
code worked for me right out of the box. I didn't get JLatham's to work
(probably user error on my part...). But it's perfect, and just what I was
looking for.

Thanks again!

Steve Vincent
(e-mail address removed)

 

[Steve Vincent]

I'm thinking that if this were tied to the workbook's BeforePrint event, then
you don't even have to bother the user with choosing and running a macro.
Like most things in Excel it depends on a few things remaining static. In
this case the thing would be the worksheet name. Mallycat, I took the
liberty of adding your code inside of a sample here:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim AnySheet As Worksheet
Dim myRange As Range
Dim CellRange As Object

For Each AnySheet In ActiveWindow.SelectedSheets
'change "Sheet1" to actual sheet name
If AnySheet.Name = "Sheet1" Then
Set myRange = AnySheet.Range("a1:c2")
For Each CellRange In myRange
CellRange.Value = CellRange.Value + 1
Next
End If
Next
'delete the next line to
'actually let it print out
'this is here just for testing
Cancel = True
End Sub

Naturally, change the sheet name and cell range to fit your needs and once
you're sure it's working, remove the Cancel=True line to actually start
sending stuff to the printer.

The 'advantage' here is that whether you choose the one sheet or a group of
sheets, the update of the values on that sheet will be made without any extra
work on the part of the user.

 

[Steve Vincent]

This works great... "but", I have two more questions:
1. How would I adjust the code to change/increment values in non-adjacent
cells (not in a range)?

and

2. How can I make this print, say, 100 copies, incrementing at each
printout, without having to run the macro or click the macro button (i know,
same thing ;-) 100 times?

(sorry about replying back to this thread twice, once without any comments.)

TIA,
Steve Vincent
(e-mail address removed)