Using VBA to print to PDF existing macro

Question

So i found this code online and was able to edit it to do what i want EXCEPT save as a PDF it currently set to only show me a print preview. Can someone explain how to edit this to save as a PDF with the File name being what ends up appearing in cell "A2"

Sub testme()

Dim TempWks As Worksheet
Dim wks As Worksheet

Dim myRng As Range
Dim myCell As Range

'change to match your worksheet name
Set wks = Worksheets("Sheet3")
Set TempWks = Worksheets.Add

wks.AutoFilterMode = False 'remove the arrows

'assumes headers only in row 1
wks.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=TempWks.Range("A1"), Unique:=True

With TempWks
    Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With wks
    For Each myCell In myRng.Cells
        .UsedRange.AutoFilter Field:=1, Criteria1:=myCell.Value
        Dim MyFileName As Variant
Dim MyfilePath As Variant
Dim rng As Range
Set wks = Worksheets("Sheet3")
Set rng = wks.Cells(2, 1)

MyfilePath = "C:\Users\mmunoz\Desktop\Teresa" 'this is whatever location you wish to save in

MyFileName = MyfilePath & "\" & rng.Value & ".pdf" 'You can do the below in just a couple of lines, but this is way more effective and stops issues later on

    ChDir _
    MyfilePath ' hold your save location


wks.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    MyFileName, Quality:= _
     xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=False 'did you want to open the file after saving?
    Next myCell
End With

Application.DisplayAlerts = False
TempWks.Delete
Application.DisplayAlerts = True

End Sub

I have a bunch of data that I need to filter to show only a client's lines of data and save that as a PDF to send to the client.

Thanks,

Possible duplicate of [Excel VBA to Export Selected Sheets to PDF](https://stackoverflow.com/questions/20750854/excel-vba-to-export-selected-sheets-to-pdf) — handras, Mar 15 '19 at 16:31

Badja · Accepted Answer · 2019-03-15T16:23:52.433

0

This is the gist of what you want. I've added comments to explain

Dim MyFileName As Variant
Dim MyfilePath As Variant
Dim rng As Range

Set rng = wks.Cells(2, 1)

MyfilePath = "N:\Desktop" 'this is whatever location you wish to save in

MyFileName = MyfilePath & "\" & rng.Value & ".pdf" 'You can do the below in just a couple of lines, but this is way more effective and stops issues later on

    ChDir _
    MyfilePath ' hold your save location


 wks.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
   MyFileName, Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True 'did you want to open the file after saving?

edited Mar 15 '19 at 16:23

answered Mar 15 '19 at 15:47

Badja

857
1
8
33

Do i just replace this with the .Printout Preview= True? I do not want it to open after publishing so i can change that to False (as there are about 200 different files it will make) I also got a "Compile Error: Syntax Error" on the MyFileName line – Michael Munoz Mar 15 '19 at 15:53
I'll amend for the range. Regarding the first bit - try it there and see what happens – Badja Mar 15 '19 at 15:59
I am still getting a syntax error and it highlights the ".pdf" part of the code And thank you for going through this with me I greatly appreciate it – Michael Munoz Mar 15 '19 at 16:21
Try now, I left an **&** out – Badja Mar 15 '19 at 16:24
That got it running. But I don't know if it held the save location. I saw it run and it ended showing the very last customer filtered but only the first customer's file got saved in the location. I checked my documents and do not see the files in there either so I am not sure where it saved them – Michael Munoz Mar 15 '19 at 16:32
This will be to do with your allocation of myRng and myCell - have a look around. Step through your script (Using F8 on your keyboard) see what happens – Badja Mar 15 '19 at 16:35
I see that its creating a list of just the "A" column in a new worksheet then filtering the Sheet 3 for each value on that list created from the macro. I saw when it got to your portion it saved the very first PDF in the correct location. Then the next 3-4 cycles .tmp files flashed in the folder but it did not create the file. – Michael Munoz Mar 15 '19 at 16:47
Try removing Dim rng etc and putting `\" myRng.Value &` so use your defined range not mine – Badja Mar 15 '19 at 16:53
ok i figured out what it is doing... since the new worksheet the macro creates has the first customer in cell "A2" it is pulling the name from that new worksheet cell. When i want it to pull "A2" from the filtered data on Sheet 3 (since the macro is filtering and saving based on Sheet 3) – Michael Munoz Mar 15 '19 at 16:53
So you should just be able to call that cell using the `Cells(2, 1)` method – Badja Mar 15 '19 at 16:57
When it hits the MyFileName again (I'm assuming cause the myRng.Value & ".pdf" it is giving me a run-time error '13': Type mismatch – Michael Munoz Mar 15 '19 at 16:58
It shouldn't, if it was working with just `rng.Value`. Was it working before? – Badja Mar 15 '19 at 17:02
i replaced it with Cells(2,1) but it is taking from the created worksheet still not from Sheet 3. How do i direct it to Sheet 3 cell(2,1).. it was working before as well with the rng.value – Michael Munoz Mar 15 '19 at 17:02
You'll need to `Set wks = ` to sheet 3, then when you `Set rng = wks.Cells(2, 1)` that will pull sheet 3, Range A2 – Badja Mar 15 '19 at 17:04
I edited my original posting to show what it looks like right now which I am assuming is incorrect. cause it is still pulling from the new tab. Sorry I do not know much about VBA at all except to google stuff when it comes to macros. – Michael Munoz Mar 15 '19 at 17:16
You need to read through your script. Add comments to bits you know are right, and it'll fill itself in. You're close to getting this right. I'm finishing work now and don't think I can help any more. Although someone else should be able to come and help and if not, yo are allowed to reopen a question, with your more specific needs with the amalgamation of my code and yours – Badja Mar 15 '19 at 17:23
1

i appreciate your help you got me right on the edge :) thanks – Michael Munoz Mar 15 '19 at 17:28
Not a problem, sorry I couldn't help further – Badja Mar 15 '19 at 17:28
i got it to work.. youre suggestion of labeling everything helped :). I just changed the rng.value to myCell.Value since that was being updated each time it ran – Michael Munoz Mar 15 '19 at 18:25
That's brilliant! Add your final code as an answer so if anyone else has the same issue, it's there – Badja Mar 15 '19 at 19:30

score 0 · Answer 2 · answered Mar 15 '19 at 20:17

Option Explicit Sub testme()

Dim TempWks As Worksheet
Dim wks As Worksheet

Dim myRng As Range
Dim myCell As Range

'change to match your worksheet name
Set wks = Worksheets("Sheet3")

Set TempWks = Worksheets.Add

wks.AutoFilterMode = False 'remove the arrows

'assumes headers only in row 1
wks.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=TempWks.Range("A1"), Unique:=True

With TempWks
    Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With wks
    For Each myCell In myRng.Cells
        .UsedRange.AutoFilter Field:=1, Criteria1:=myCell.Value
Dim MyFileName As Variant
Dim MyfilePath As Variant
Dim rng As Range

Set rng = wks.Cells(2, 1)

MyfilePath = "C:\Users\mmunoz\Desktop\Teresa" 'File Location

MyFileName = MyfilePath & "\" & myCell.Value & ".pdf" 'File Name

    ChDir _
    MyfilePath


wks.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
  MyFileName, Quality:= _
     xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
  OpenAfterPublish:=False
    Next myCell
End With

Application.DisplayAlerts = False
TempWks.Delete
Application.DisplayAlerts = True

End Sub

Using VBA to print to PDF existing macro

2 Answers2