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/thread...rking.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).