Dear Excel,
I have the ultimate challenge: to do job books for 8,000 employees.
The form is completed, but the request is to make booklets of 52 pages
for each employee.

Each page of the booklet must also have each employee's number on it
(0001 to 8000). Each page o fthe booklet must be numbered with the
week number from 01 to 52. Therefore I need to auto generate 416,000
pages. Ridiculous I know but nobody listens to us!

How can I export the above scenario to PDF from Excel?
Thanks

On Apr 19, 2:17*pm, gatecrasherg13 gatecrasherg13
wrote:
Dear Excel,
I have the ultimate challenge: to do job books for 8,000 employees.
The form is completed, but the request is to make booklets of 52 pages
for each employee.

Each page of the booklet must also have each employee's number on it
(0001 to 8000). *Each page o fthe booklet must be numbered with the
week number from 01 to 52. *Therefore I need to auto generate 416,000
pages. *Ridiculous I know but nobody listens to us!

How can I export the above scenario to PDF from Excel?
Thanks

I found the following code from http://help.lockergnome.com/office/s...ict993043.html
However, 1) the code doesn't work for me and 2) that registry folder
does not exist on my computer (if I create it, what exact path would
it go in anyway?)
Isn't there any simpler solution to my question above? Surely this
must be a common request?! Thanks for all feedback.
Yours


In the Thisworkbook section of the VB is the code

Public Sub Workbook_Open()
Worksheets("invoice").Range("d1") = False
End Sub

' Disable the general sheet printing function so its all controlled by
the print button macro



Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Worksheets("invoice").Range("D1") = False Then
Cancel = True
End If
End Sub


Then create a button on the worksheet with the code

Private Sub CommandButton1_Click()

Dim CopiesCount As Long
Dim CopieNumber As Long
Dim nNumber As Long
' Set the location of the registry entry to hold the sequential number
outside Excel
'use Start-- run-- regedit to open the registry
'registry location is 'Software--VB and VDA Program Settings--Excel--
"Invoice"--Invoice_Key"
'"Invoice" is the sheet name used in this example it needs to be your
sheet name.
'"invoice Key" is just a registry entry name - it can be anything you
want for your application
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "Invoice"
Const sKEY As String = "Invoice_key"
Const nDEFAULT As Long = 1&
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT) 'retreive
the last sheet number printed from
' the registry

' Ask the user how many copies of the template to print
'You can modify this line to say default print 100 copies or whatever
you need




CopiesCount = Application.InputBox("How many Copies do you want to
print?", , 1, Type:=1)

For CopieNumber = nNumber To (nNumber + (CopiesCount - 1))

With ThisWorkbook.Sheets("Invoice")

With .Range("B2")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)
..NumberFormat = "@"
..Value = Format(nNumber, "0000")
Else
..Value = Format(CopieNumber, "0000")
End If
End With

Worksheets("invoice").Range("D1") = True
'Print the sheet
..PrintOut
End With

Next
nNumber = CopieNumber
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber&
Worksheets("invoice").Range("D1") = False
End Sub

On Apr 19, 2:17*pm, gatecrasherg13 gatecrasherg13
wrote:
Dear Excel,
I have the ultimate challenge: to do job books for 8,000 employees.
The form is completed, but the request is to make booklets of 52 pages
for each employee.

Each page of the booklet must also have each employee's number on it
(0001 to 8000). *Each page o fthe booklet must be numbered with the
week number from 01 to 52. *Therefore I need to auto generate 416,000
pages. *Ridiculous I know but nobody listens to us!

How can I export the above scenario to PDF from Excel?
Thanks

Can I configure my form using a lookup table for the week (01 - 52)
and employee number (0001 - 8000)? Can I export from Excel so that it
prints every combination of those two lookups?
Thank you very much

I'll make an offer: I'll help you cut down a large portion of a good sized
forest to print these things if you'll get in touch with me via emal at
(remove spaces):
Help From @JLatham Site. com
Make a reference to your posting here, or at least mention 'gatecrasher13'
and remind me of what it is you need to do. We can start figuring out how to
get it done.
Tell me:
What version of Excel you are using;
What version of Windows you are using;
and what do you have installed on your system as a PDF printer.
If at all possible, a copy of the file to be printed would be MOST!!
helpful, or at least something with the same number of sheets and layout on
them even if the data is somehow dummied up.

"gatecrasherg13 gatecrasherg13" wrote:

On Apr 19, 2:17 pm, gatecrasherg13 gatecrasherg13
wrote:
Dear Excel,
I have the ultimate challenge: to do job books for 8,000 employees.
The form is completed, but the request is to make booklets of 52 pages
for each employee.

Each page of the booklet must also have each employee's number on it
(0001 to 8000). Each page o fthe booklet must be numbered with the
week number from 01 to 52. Therefore I need to auto generate 416,000
pages. Ridiculous I know but nobody listens to us!

How can I export the above scenario to PDF from Excel?
Thanks

Can I configure my form using a lookup table for the week (01 - 52)
and employee number (0001 - 8000)? Can I export from Excel so that it
prints every combination of those two lookups?
Thank you very much
.