CODEWITHSUNDEEP
vbaexcelpdf
sleven
sleven

Reputation: 107

Saving to PDF loop file name issue

I have a macro that loops through a list of values and saves each as a PDF with the name being the value in that list in cell A7. It is working perfectly as it is, but I am having trouble adjusting the file name to include the value in A6 as well. I am relatively new to using VBA so this is probably an easy fix. Here's the working code:

Dim myFolder As String
Dim myFileName As String

myFolder = "C:\Users\JonDoe\Documents\"

For Each dealer In Range("S8:S15")
        Range("A7").Value = dealer
        myFileName = dealer & ".pdf"
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        myFolder & myFileName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False

Next
End Sub

I tried to add the value in A6 to myFileName like so:

Sub printDealerPagePDF()

Dim myFolder As String
Dim myFileName As String

myFolder = "C:\Users\JonDoe\Documents\"

For Each dealer In Range("S8:S15")
        Range("A7").Value = dealer
        Range("A6").Value = dealername
        myFileName = dealer & dealername & ".pdf"
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        myFolder & myFileName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False

Next
End Sub

The macro still runs without error but it doesn't add "dealername" to the file name. What am I missing?

Upvotes: 0

Views: 301

Answers (2)

CLR
CLR

Reputation: 12279

I think * what you're trying to do is loop through each value from Range("S8:S15") into A7 and print the page off as a PDF file.

You appear to be asking why the dealername isn't present. It's because you're not defining it. Where is it? Is there a formula in A6 that is perhaps a VLOOKUP that gathers it?

In which case 

 dealername = Range("A6").Value
 Range("A7").Value = dealer

should work.

However, if it's alongside each dealer (in the Range("S8:S15")) - perhaps in column T and needs to be loaded to A6 before printing then use:

 dealername = dealer.Offset(0,1).Value
 Range("A7").Value = dealer
 Range("A6").Value = dealername

.* But I could be entirely wrong

Upvotes: 0

C. Henke
C. Henke

Reputation: 161

The Problem is that you do not actually assign a Value to "dealername".

dealername = Range("A6").Value

The above line of code should fix the problem. (The same goes for dealer)

Upvotes: 3

Related Questions