Increment a number in a cell for each page printed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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
 
Steve said:
Any ideas where to start?
Thank you in advance,
Steve Vincent
(e-mail address removed)
Click to expand...

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
 
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.
 
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)
 
JLatham said:
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.
Click to expand...
 
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)
 
Back
Top