Increment a number in a cell for each page printed
 

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

Increment a number in a cell for each page printed
 

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 Vincent" wrote in message
...
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

Increment a number in a cell for each page printed
 

Steve Vincent Wrote:

Any ideas where to start?
Thank you in advance,
Steve Vincent


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/~alli...dThenPrint.xls

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=552845

Increment a number in a cell for each page printed
 

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 he

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.

"Mallycat" wrote:


Steve Vincent Wrote:

Any ideas where to start?
Thank you in advance,
Steve Vincent


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/~alli...dThenPrint.xls

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=552845

Increment a number in a cell for each page printed
 

"JLatham" wrote:

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 he

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.

"Mallycat" wrote:


Steve Vincent Wrote:

Any ideas where to start?
Thank you in advance,
Steve Vincent


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/~alli...dThenPrint.xls

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=552845

Increment a number in a cell for each page printed
 

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


"JLatham" wrote:

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 he

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.

"Mallycat" wrote:


Steve Vincent Wrote:

Any ideas where to start?
Thank you in advance,
Steve Vincent


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/~alli...dThenPrint.xls

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=552845

Increment a number in a cell for each page printed
 

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


"Mallycat" wrote:


Steve Vincent Wrote:

Any ideas where to start?
Thank you in advance,
Steve Vincent


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/~alli...dThenPrint.xls

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=552845