Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula question
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula question
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. "iarnold" wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula question
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. -- Rick (MVP - Excel) "iarnold" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula question
Thanks
Do I paste this formula in the particular cell or somewhere else? "Sean Timmons" wrote: 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. "iarnold" wrote: 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula question
Well, that's cleaner than mine!
"Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "iarnold" wrote in message ... 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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula question
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. :-) "iarnold" wrote: Thanks Do I paste this formula in the particular cell or somewhere else? "Sean Timmons" wrote: 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. "iarnold" wrote: 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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula question
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. -- Rick (MVP - Excel) "Sean Timmons" wrote in message ... 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. :-) "iarnold" wrote: Thanks Do I paste this formula in the particular cell or somewhere else? "Sean Timmons" wrote: 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. "iarnold" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Question | Excel Discussion (Misc queries) | |||
Formula question | Excel Discussion (Misc queries) | |||
Formula Question | Excel Discussion (Misc queries) | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
formula question | Excel Discussion (Misc queries) |