Help modifying an existing Macro that saves as PDF

Contributor

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

7 Replies

@Budman361530 

It should work if you change

 

ActiveSheet.ExportAsFixedFormat

 

to

 

Range("AO1:BA47").ExportAsFixedFormat

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

@Budman361530 

You forgot the quotes:

 

Range("AO1:BA47")

Hans,
Cannot thank you enough. That was it. Works prefect!!! You sir have a wonderful week!
Hey Hans,
One last question. Currently this Macro names this "Change Order" then the file name of the workbook. I want to use this Macro on more then one sheet in this book. How can I get it to use the active sheet name vs. "Change Order". Example sheet one is Change Order 1. Sheet two is Change Order 2... etc. I just realized each time I print/save the PDF, it overwrites the original PDF.

@Budman361530 

 

Change the part

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

to

strPath = ThisWorkbook.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "/"

'Getting the file name dynamically
strName = ActiveSheet.Name

'Creating the new file location and name
strFile = strName & ".pdf"
strPathFile = strPath & strFile
Hans, Again, THANK YOU! I was so close.... Modified what you gave to fit my application, but what you showed me, got me to see what I was doing wrong. I was deleting the strPath completely.

strPath = strPath & "/"

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