how to +1 in a cell for every page printed

[Geoff A]

I have a need to do the following;

print 5000 pages of the same worksheet, but one cell needs
to be incremeted by 1 every time it prints. Kind of like
page count but within a single cell.

btw, my cell is text followed by numbers ie..ABCD10001
the next print it would need to be ABCD10002 and then
ABCD10003 and so on and so on

 

[jr]

In case no one suggests a built in method, here's one
method that I came up with. I take it you want to
increment a cell each time it prints and that you want
this cell to also be printed.
A1 will be incremented cell
in two unused cells (say, K1 and L1) enter:
K1=0 (zero) to initiate counter
L1=1
in A1 the incrementing cell enter:
A1=K1+L1 (this will initialize A1 to 1)

write a macro to:
1) PRINT
2) copy the VALUE of A1 to K1
3) GOTO A1
4) EDIT | COPY
5) GOTO K1
6) PASTE SPECIAL | VALUE
7) repeat 1 thru 6 as needed

 

[Dave Peterson]

How about a small macro:

Option Explicit
Sub testme()

Dim myCell As Range
Dim iCtr As Long
Dim myText As String
Dim myNumber As Variant

With Worksheets("sheet1")
Set myCell = .Range("a1")
myText = Left(myCell.Value, Len(myCell.Value) - 5)
myNumber = Right(myCell.Value, 5)
If IsNumeric(myNumber) = False Then
MsgBox "error in: " & myCell.Address(0, 0)
Exit Sub
End If
For iCtr = 1 To 5 '5000
myNumber = myNumber + 1
myCell.Value = myText & Format(myNumber, "00000")
.PrintPreview '
Next iCtr
End With

End Sub

I used sheet1 cell A1. I also assumed that the text could be any length, but
the numbers were exactly 5 digits. If the values actually look like: ABCD1,
ABCD12, ABCD1245, AQWASDABCD1000132, then more work would be needed.

And if you're new to macros, you can read some notes at David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/getstarted.htm