Sleeper: Printing to PDF

[mdmackillop]

I'm trying to print from up to six spreadsheets to a single pdf file. Selecting all the sheets and printing does not work as it should, so I'm trying for a workaround. The following code snippet should print each spreadsheet to the same temporary file name (OldName), this is then renamed to sequentially numbererd files and these are finally combined using a PDF exe file. I've built in some delay loops but still finding inconsistent results, as per the first item from the debug file. Any ideas/suggestions?

'Print out separate pdf files and rename to consecutive numbers
'Delete numbered files on completion
OldName = Temp & "Valbook.pdf"
For j = 1 To i
If MySheets(j) = "Letterhead" Then
Sheets(MySheets(j)).PrintOut From:=1, To:=1
Else
Sheets(MySheets(j)).PrintOut
End If
'Loop to permit printing to complete
Wait = Timer
Do
DoEvents
Loop Until Timer - Wait > 3
Debug.Print Dir(OldName)
NewName = Temp & "Valbook" & j & ".pdf"
PDFList = PDFList & NewName & " "
Name OldName As NewName
Wait = Timer
Do
DoEvents
Loop Until Timer - Wait > 2
Debug.Print MySheets(j) & " - " & Dir(NewName)
Next
PDFList = "C:\pdf995\combine.exe " & PDFList & OldName
'Combine PDFs into a single document; move to JobFolder
Shell PDFList
Debug output
Valbook.pdf
Letterhead - 
Valbook.pdf
tmpFinStat - Valbook2.pdf
Valbook.pdf
tmpInstruct - Valbook3.pdf
Valbook.pdf
tmpVaries - Valbook4.pdf
Valbook.pdf
Certificate - Valbook5.pdf

[Cyberdude]

While you're waiting for an answer, I would like to know why you need a timer. Is "printout" an asynchronous event (asynchronous to the macro execution)?

[mdmackillop]

The timer is an attempt to get things working, although I've tried other loops, eg

Loop until dir(oldname) <> ""

Here's my debug result without the timer

Letterhead - 
tmpFinStat - 
tmpInstruct - 
Valbook.pdf
tmpVaries - Valbook4.pdf
Certificate -

[mdmackillop]

It appears that my problem is not only with the printing, but with making a copy of sheets which are to be modified then printed. The copies were not being created, hence could no be printed. The code is running from a Userform in an XLA and one of the subs includes the following:

'Make a copy of sheets to be changed
With ActiveWorkbook
.Sheets("Instruct").copy Before:=Sheets(1)
.ActiveSheet.Name = "tmpInstruct"
.Sheets("Varies").copy Before:=Sheets(1)
.ActiveSheet.Name = "tmpVaries"
.Sheets("FinStat").copy Before:=Sheets(1)
.ActiveSheet.Name = "tmpFinStat"
.Sheets("Claim").copy Before:=Sheets(1)
.ActiveSheet.Name = "tmpClaim"
.Sheets("FinAcc").copy Before:=Sheets(1)
.ActiveSheet.Name = "tmpFinAcc"
End With

I was running this without "With Activeworkbook", as I'm only working within the one workbook, should this make any difference?