Excel to PDF

[I Maycotte]

Below is a modified code from a recorded macro whose purpose is to print
to a pdfdriver and then save. Manually this takes a long time when the
user deals with more than a handful cells. In my case, my range
consists of 200+ cells. Everything seems correct with one exception --
the file is created but no data is saved. That is to say that when I
open "My Documents" I see a properly named PDF file but of size 0KB
(should be roughly 4.5 KB). When I attempt to open it, I receive a
file read error. I am wondering if there is anything wrong with the
code or if it's a problem with my writer. I am hesitant to say that it
is the writer because if I do each file manually, they are created and
viewed without error. Thank you all who reply.



Code:
--------------------
Sub PrintToPDF()
'
'
Dim Products As Range
Dim Filename As String

Set Products = Worksheets("Summary").Range("Products")

For Each cell In Products
If IsEmpty(cell) Then Exit For

Filename = cell
cell.Copy

Worksheets("Historical").Paste Destination:=Worksheets("Historical").Cells(2, 1)

Application.CutCopyMode = False
Application.Calculate

Application.ActivePrinter = "Acrobat PDFWriter on LPT1:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Acrobat PDFWriter on LPT1:", PrintToFile:=True, PrToFileName:="C:\My Documents\" & Filename & ".pdf"

Next cell
End Sub

 

[Guest]

why not try changing

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Acrobat PDFWriter on LPT1:", PrintToFile:=True, PrToFileName:="C:\My
Documents\" & Filename & ".pdf"

to

Worksheets("Historical").PrintOut Copies:=1, ActivePrinter:= _
"Acrobat PDFWriter on LPT1:", PrintToFile:=True, PrToFileName:="C:\My
Documents\" & Filename & ".pdf"

Also make sure if there is a printarea defined for the sheet Historical, it
is set to the correct area.

 

[I Maycotte]

Thanks for the help, Tom. It works. Very much appreaciated.

-- Isaac Maycott