Issue saving Excel Worksheet to .pdf

[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

[Aussiebear]

Welcome to VBAX kpost. Someone will be along shortly to assist you in this matter.

In the mean time this is how I would save a workbook as a pdf file



Sub SaveActiveWorkbookAsPDF()
'Create and assign variables
Dim saveLocation As String
saveLocation = "C:\Users\JimmyCrickets\Documents\myPDFFile.pdf"
'Save active workbook as PDF
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation
End Sub

[kpost]

I tried that and it saved the .pdf as the file name that I specified. But 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

[kpost]

na

[Aflatoon]

FYI, cross-posted here: https://www.mrexcel.com/board/threads/excel-macro-to-save-to-pdf-not-working.1255831/

[Aussiebear]

Well that's disappointing.

[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

[Aussiebear]

@kpost, what no apologies for cross posting without notifying us? Until you do, I'm afraid I cant be bothered assisting any further.

[Aflatoon]

He cross-posted in at least two other forums and has apparently had an answer in one of them. He's been banned at MrE for ignoring the rules (and attitude about it).