Need help with looping through a list and saving each to unique PDF file

[dkone]

I think I am close on this, but it is not quite working as intended. The first version of this VBA was originally created to loop through a list on the "CertData" sheet and print the output as a defined range on sheet "Certificate". This worked perfectly, but I would rather have it loop through the list and save each range as a PDF file. The code below kind of works, I can see it on the screen as it loops through all the rows from the "CertData" sheet, but it only saves a single PDF file and that is the data from the last row, but names the file from data in the first row. I am really scratching my head on this one as it seems the line rng.export... should fire off with each step of the loop just like it did when the output was set to PrintOut.

Sub PrintCerts()

Dim i As Long, LastRow As Long
Dim SaveLocation As String
Dim rng As Range

SaveLocation = "C:\Users\dkone\Desktop\PDF_Print_Output\": NewName = SaveLocation & Range("P8").Text & "_" & Range("P10").Text & ".PDF"

Set rng = Worksheets("Certificate").Range("A1:M39")

LastRow = Worksheets("CertData").Range("A65536").End(xlUp).Row


For i = 1 To LastRow

If Not Worksheets("CertData").Range("A" & i).Value = 0 Then
    Worksheets("Certificate").Range("P5").Value = Worksheets("CertData").Range("A" & i).Value
    rng.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=NewName
'    Worksheets("Certificate").Range("A1:M39").PrintOut - this line worked fine with the loop and I would get 1 printout for each step of the loop, when this line was used, I wasn't using the Dim rng
End If
Next i

End Sub

 

[Micron]

Whenever code doesn't work as expected, put a break point on a line (safest would be the first line), step through it (F8) and watch your variable values and references. Note that a line must be processed before any variable or reference will set/change. You can also inquire in the immediate window as in
?myVariable
and hit Enter to process the inquiry. It is possible that a condition in your IF test is never true until the end, or there is some other aspect that doesn't happen when you think it should. Nothing is obvious to me in that code, except possibly that the value is never 0 but is either "" or is "0".

 

[dkone]

Whenever code doesn't work as expected, put a break point on a line (safest would be the first line), step through it (F8) and watch your variable values and references. Note that a line must be processed before any variable or reference will set/change. You can also inquire in the immediate window as in
?myVariable
and hit Enter to process the inquiry. It is possible that a condition in your IF test is never true until the end, or there is some other aspect that doesn't happen when you think it should. Nothing is obvious to me in that code, except possibly that the value is never 0 but is either "" or is "0".

Thanks, your suggestion helped, mainly because it made me rethink the issue, which ultimately led to the solution. The problem was two-fold. First, having the variables for the file name outside of the loop. This was causing only 1 pdf file to be created with variables from row 1, so by the time I got to the last row the file name was from row one and the data in the PDF was from the last row. Solution was to move the file name with variables into the loop. The second problem and this one was tricky because the error message I was getting was not that of the problem. The text in some of the variables for the filename had illegal characters for filenames. Solution for that was simple, just changed the text in the spreadsheet and replaced the illegal characters. The error message I was getting was something like 'the file is open or already exists'.

Anyway if anyone is searching for a solution to a similar problem, here is the code that works:

Sub PrintCerts()

Dim i As Long, LastRow As Long
Dim SaveLocation As String
Dim rng As Range

SaveLocation = "\\kpe-fs-01\Testing\TestingDepartment\ClassRoster\AutoSavedCerts\"

Set rng = Worksheets("Certificate").Range("A1:M39")

LastRow = Worksheets("CertData").Range("A65536").End(xlUp).Row


For i = 1 To LastRow

If Not Worksheets("CertData").Range("A" & i).Value = 0 Then
    Worksheets("Certificate").Range("P5").Value = Worksheets("CertData").Range("A" & i).Value
    rng.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=SaveLocation & Range("P8").Text & "_" & Range("P10").Text & ".PDF"
End If
Next i

End Sub

 

[Micron]

Glad to see that you were able to solve it. Even better, I think you now have a valuable troubleshooting 'tool' for next time.