Forum Discussion

Budman361530's avatar
Budman361530
Copper Contributor
Sep 05, 2022

Help modifying an existing Macro that saves as PDF

I have been using a Macro for sometime that can print (saving as a PDF) a specific page, as a specific file name in whatever folder you are working with the program in.   I would like to modify the Macro to only save/print (PDF) for a specific range.  This range is....  "AO1:BA47".  Everything I am trying gives me an error, and or tries to print many pages.   Below is the working Macro. 

 

Sub MacroContractQuote()
Dim strPath As String
Dim strName As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
strPath = ThisWorkbook.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "/Contract Quote "

'Getting the file name dynamically
strName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))

'Creating the new file location and name
strFile = strName & ".pdf"
strPathFile = strPath & strFile
'export to PDF in current folder
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=strPathFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& strPathFile
'MsgBox ("New file: " & strPathFile)
End Sub

Macros and VBA

7 Replies

Sort By
    • Budman361530's avatar
      Budman361530
      Copper Contributor
      Sep 05, 2022
      Same problem. Excel is making me change it. See below. Won't let me put the ":". Only ","

      Sub Printchangeorder()
      Dim strPath As String
      Dim strName As String
      Dim strFile As String
      Dim strPathFile As String
      Dim myFile As Variant
      strPath = ThisWorkbook.Path
      If strPath = "" Then
      strPath = Application.DefaultFilePath
      End If
      strPath = strPath & "/Change Order "

      'Getting the file name dynamically
      strName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))

      'Creating the new file location and name
      strFile = strName & ".pdf"
      strPathFile = strPath & strFile
      'export to PDF in current folder
      Range(AO1, BA47).ExportAsFixedFormat _
      Type:=xlTypePDF, _
      FileName:=strPathFile, _
      Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, _
      OpenAfterPublish:=False

      'confirmation message with file info
      MsgBox "PDF file has been created: " _
      & vbCrLf _
      & strPathFile
      'MsgBox ("New file: " & strPathFile)
      End Sub

Resources