how to +1 in a cell for every page printed

  • Thread starter Thread starter Geoff A
  • Start date Start date
G

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
 
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
 
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
 
Back
Top