Formula question

  • Thread starter Thread starter iarnold
  • Start date Start date
I

iarnold

I am trying to find a formula that will advance the numerical value of a cell
by 1 when I print a document - its for an invoice template, so that the
invoice number advances. Because I have a number of worksheets in this
workbook, I would want them all to advance by 1 when I print.

Help!
 
For i = 1 to 20
For j = 1 to 20
Sheets("Sheet"&j).Select
Range("G40").Value = i
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1,
Copies:=1,
Collate_:=True
Next j
Next i

Will spin you through 20 sheets 20 times, changing the value in G40 of each
cell to the new page #. This assumes your pages are labelled "Sheet1",
"Sheet2", "Sheet3", etc.
 
You can do that using VBA event code. Right click the icon immediately to
the left of the File item on Excel's menu bar and select View Code from the
pop up menu that appears. Copy/Paste this code into the code window that
appeared (it is the code window for ThisWorkbook)...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Worksheets("Sheet1").Range("A1")
.Value = .Value + 1
End With
End Sub

Change the Sheet1 and A1 references in the Worksheets and Range function
calls in the With statement to the worksheet name and cell address where you
want this counter placed.
 
Well, that's cleaner than mine!

Rick Rothstein said:
You can do that using VBA event code. Right click the icon immediately to
the left of the File item on Excel's menu bar and select View Code from the
pop up menu that appears. Copy/Paste this code into the code window that
appeared (it is the code window for ThisWorkbook)...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Worksheets("Sheet1").Range("A1")
.Value = .Value + 1
End With
End Sub

Change the Sheet1 and A1 references in the Worksheets and Range function
calls in the With statement to the worksheet name and cell address where you
want this counter placed.
Click to expand...
 
Please see Rick Rothstein's post below. Much cleaner than mine, really.

You'd right-click on the tab name and click View Code, as he explans. :-)
 
You'd right-click on the tab name and click View Code, as he explans. :-)

Not on the tab name; rather, on the icon next to (on the left of) the File
item on the menu bar... then click View Code. Doing it that way takes you
directly to the ThisWorkbook code window.
 
Back
Top