Excel Macro to Save to .pdf not working

[kpost]

I am working on adjusting an Excel file and am trying to get the save to .pdf to work and the macro runs with no errors but the save to .pdf is not working. Below is the VBA script

Sub Printpdf()
'
' Printpdf Macro
' Saves each sheet as a pdf
'
' Keyboard Shortcut: Ctrl+w
'
'Set X = 6 for start of loop
x = 6
Do Until x = 250
'Check for blank row in data sheet and stop looping
Sheets("data sheet").Select
Cells(x, 1).Select
If Cells(x, 1) = 0 Then
x = 250
Else
Sheets("scorecard").Select
'Copy Supplier name from "data sheet" row "x" to "scorecard" sheet
Sheets("data sheet").Select
Cells(x, 1).Select
Selection.Copy
Sheets("scorecard").Select
Range("E2").Select
ActiveSheet.Paste
' Set variable s equal to supplier name in "scorecard" sheet
s = Range("M5").Value
' Save the sheet as a pdf with the name "s"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
s, Quality:=xlQualityStandard, IncludeDocProperties _
:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
saveLocation = "C:\Users\kpost\Downloads"
'Increment value of x for loop
x = x + 1
End If
Loop
End Sub

 

[kpost]

I have updated the macro and it saves if I specify a file name in the script. However, if I want to save the file name based on a cell in this instance cell M5 it does not save. Also when I set the print area for the macro it is saving 26 pages and not just one. Below is the updated Macro:

Sub Printpdf()
'
' Printpdf Macro
' Saves each sheet as a pdf
'
' Keyboard Shortcut: Ctrl+w
'
'Set X = 6 for start of loop
    x = 6
    Do Until x = 250
'Check for blank row in data sheet and stop looping
    Sheets("data sheet").Select
    Cells(x, 1).Select
    If Cells(x, 1) = 0 Then
    x = 250
    Else
    Sheets("scorecard").Select
'Copy Supplier name from "data sheet" row "x" to "scorecard" sheet
    Sheets("data sheet").Select
    Cells(x, 1).Select
        Selection.Copy
        Sheets("scorecard").Select
        Range("E2").Select
        ActiveSheet.Paste
' Set variable s equal to supplier name in "scorecard" sheet
    s = Range("M5").Value
' Save the sheet as a pdf with the name "s"
    Dim Path, FileName1 As String
Path = "C:\Users\kpost\Downloads\PDFTest" '<-- edit path as required.
FileName1 = Sheet1.Range("M5").Value '<-- edit cell target as required.
'Save active workbook as PDF
ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
'Increment value of x for loop
    x = x + 1
    End If
    Loop
End Sub

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Issue saving Excel Worksheet to .pdf

I am working on adjusting an Excel file and am trying to get the save to .pdf to work and the macro runs with no errors but the save to .pdf is not working. Below is the VBA script Sub Printpdf() 'Printpdf Macro ' Saves each sheet as a pdf 'Keyboard Shortcut: Ctrl+w Set X = 6 for start...

www.vbaexpress.com

Excel Macro Save to .pdf not working

I am working on adjusting an Excel file and am trying to get the save to .pdf to work and the macro runs with no errors but the save to .pdf is not working. Below is the VBA script Sub Printpdf() ' ' Printpdf Macro ' Saves each sheet as a pdf ' ' Keyboard Shortcut: Ctrl+w...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Also, you should be aware that these rules are the same on most forums, so I suggest you add relevant links in your posts in the other forums too.

 

[RoryA]

Which module is the code in, and what exactly is in M5 on the scorecards sheet - a file name, or full path as well?

 

[kpost]

Hello Rory. The module is just in Standard. The M5 is a name in a field.

 

[RoryA]

I take it you mean a normal module, not a worksheet called Standard?

If M5 is only a file name and you aren't getting errors, then most likely the pdf is being created, just not where you expect. It will be going to whatever Excel considers to be the current directory. If you open the VB Editor, and go to the immediate window and enter:

?Curdir

and press Enter, what does it output?

 

[RoryA]

Also, please add ALL the necessary links now for your crossposts in other forums.

 

[kpost]

I have adjusted the script and it saves one file. However I need to have several worksheets to save when I trigger the macro. Here is some added backend information. There are two other macros that I have to click first and what each of them do is create a new worksheet scorecard. How can I go about saving each of those score cards automatically with the specified field name in M5 when my print to .pdf macro is clicked? I figured the rest of the maco that I had originally posted would accomplish that but it does not appear it does. It just saves one scorecard file.

Sub Printpdf()
'
' Printpdf Macro
' Saves each sheet as a pdf
'
' Keyboard Shortcut: Ctrl+w
'
'Set X = 6 for start of loop
x = 6
Do Until x = 250
'Check for blank row in data sheet and stop looping
Sheets("data sheet").Select
Cells(x, 1).Select
If Cells(x, 1) = 0 Then
x = 250
Else
Sheets("scorecard").Select
'Copy Supplier name from "data sheet" row "x" to "scorecard" sheet
Sheets("data sheet").Select
Cells(x, 1).Select
Selection.Copy
Sheets("scorecard").Select
Range("E2").Select
ActiveSheet.Paste
' Set variable s equal to supplier name in "scorecard" sheet
s = Range("D2").Value
saveLocation = "C:\Users\kpost\Downloads\"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
saveLocation & s, Quality:=xlQualityStandard, IncludeDocProperties _
:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
'Increment value of x for loop
x = x + 1
End If
Loop
End Sub

 

[RoryA]

Please do as I asked regarding links to other cross-posts.

 

[kpost]

Rory that is okay I got it worked with no assistance from your forum. Great support and customer service by the way.