Excel VBA: Printing x copies each with an increasing number in a cell

Question

Is it possible to either:

• Increase a number in a cell each time a page has been printed: e.g. print 20 pages with numbers from 1-20 in the cell

  or if that isn't possible:

• Run a For-loop, each increasing the number and each time prompting the printer to print.

I know this is a very basic question, but bear with me - It is the 1st time I'm trying my luck at Excel macros.

Cheers

Answer 1

This code can be put into 'ThisWorkbook' object to increment a cell value each time something from the workbook is printed.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
 ThisWorkbook.Sheets("Sheet1").Range("A1").Value = _
  ThisWorkbook.Sheets("Sheet1").Range("A1").Value + 1
End Sub

Answer 2

It is possible to use a VBA Macro to populate and print a worksheet. For Excel 2010, use the Developer ribbon to Record Macro. It will record the steps you want in a Visual Basic macro, which you can then edit. You can change the macro to use a For-loop, changing the worksheet each time before you print.