Save 2 Worksheets As PDF

[leewoo5]

Hi all,

I have the following code that I would like to adapt to change from saving one worksheet to saving two as a pdf.

The worksheet names are "NCR" and "RCA" and the code is as follows:

Sub SaveAsPDFExcel()






Dim fName As String
Dim sLoc As String 'location




    sLoc = "I:\FST\Quality\Non-Conformance\Non-Conformance Reports\Open\"




With ActiveSheet
    fName = .Range("L4").Value & "-" & .Range("E6").Value
    
    'Save as xlsx
    ActiveWorkbook.SaveAs Filename:=sLoc & fName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            sLoc & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
            
            
End With
End Sub

Any help will be greatly appreciated.

Many thanks,

 

[ZVI]

Hi,
Try this:

Sub SaveAsPDFExcel()
 
  Dim fName As String
  Dim sLoc As String  'location
 
  sLoc = "I:\FST\Quality\Non-Conformance\Non-Conformance Reports\Open\"
 
  ' Select both sheets to export into PDF file
  Sheets(Array("NCR", "RCA")).Select
 
  With ActiveSheet
   
    fName = .Range("L4").Value & "-" & .Range("E6").Value
 
    'Save as xlsm
    ActiveWorkbook.SaveAs Filename:=sLoc & fName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
   
    ' Export to the PDF
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                         sLoc & fName, Quality:=xlQualityStandard, _
                         IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
   
    ' Unselect the group (select only one sheet)
    .Select
 
  End With
 
End Sub

Regards

 

[Jason Barnes]

ZVI

I'm simply trying to save as pdf.
I tried your code using my own paths and I'm getting an error,

Run-time error '1004':
Document not saved. The document may be open, or an error may have been encountered when saving.

Any suggestions?

Sub SaveAsPDFExcel()
 
  Dim fName As String
  Dim sLoc As String  'location
 
  sLoc = "C:\Users\Jason Barnes\Desktop\Denbury"
 
  ' Select sheet to export into PDF file
  Sheets("MAIN").Select
 
  With ActiveSheet
   
    fName = .Range("BC3").Value
 
    'Save as xlsm
    ActiveWorkbook.SaveAs Filename:=sLoc & fName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
   
    ' Export to the PDF
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                         sLoc & fName, Quality:=xlQualityStandard, _
                         IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
                         
  End With
End Sub

 

[ZVI]

The backslash symbol (actuall a path separator) has been omitted at the end of sLoc. Try this modification of your code^

Sub SaveAsPDFExcel()
 
  Dim fName As String
  Dim sLoc As String  'location
 
  ' Don't forger the backslash symbol "\" at the end of the location path !
  sLoc = "C:\Users\Jason Barnes\Desktop\Denbury\"
 
  ' Get file name
  fName = Sheets("MAIN").Range("BC3").Value
 
  'Save as xlsm
  ActiveWorkbook.SaveAs Filename:=sLoc & fName, _
                        FileFormat:=xlOpenXMLWorkbookMacroEnabled
 
  ' Disable screen updating
  Application.ScreenUpdating = False
 
  ' Select sheets for exporting into the PDF file
  Sheets(Array("NCR", "RCA")).Select
 
  ' Export to the PDF
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                  Filename:=sLoc & fName, _
                                  Quality:=xlQualityStandard, _
                                  IncludeDocProperties:=True, _
                                  IgnorePrintAreas:=False, _
                                  OpenAfterPublish:=False
 
  ' Select the MAIN sheet
  Sheets("MAIN").Select
 
  ' Restore screen updating
  Application.ScreenUpdating = True
 
End Sub

Regards