Macro to save worksheet as PDF

[CAPtain232]

Can someone help me write a macro to save the active worksheet as a PDF with filename referencing cell A1





Thank you

 

[John_w]

This includes the workbook name before "Multiple Sheets.pdf":

Public Sub Save_Multiple_Sheets_As_One_PDF()

    Dim currentSheet As Worksheet
    Dim p As Long
    
    With ThisWorkbook
        Set currentSheet = .ActiveSheet
        .Worksheets(Array("Sheet5", " Sheet6", " Sheet8", " Sheet10", " Sheet11", " Sheet14", " Sheet15")).Select
        p = InStrRev(.FullName, ".")
        .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Left(.FullName, p - 1) & " Multiple Sheets.pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        currentSheet.Select
    End With
    
End Sub

 

[hadham]

This includes the workbook name before "Multiple Sheets.pdf":

Public Sub Save_Multiple_Sheets_As_One_PDF()

    Dim currentSheet As Worksheet
    Dim p As Long
    
    With ThisWorkbook
        Set currentSheet = .ActiveSheet
        .Worksheets(Array("Sheet5", " Sheet6", " Sheet8", " Sheet10", " Sheet11", " Sheet14", " Sheet15")).Select
        p = InStrRev(.FullName, ".")
        .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Left(.FullName, p - 1) & " Multiple Sheets.pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        currentSheet.Select
    End With
    
End Sub

Did not work, it give a bug message

 

[John_w]

What is the exact error message?

Which line caused it? To find out, click Debug on the error message and the line is highlighted in yellow.

 

[hadham]

The error "DEBUG", and the below is marked in red:

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Left(.FullName, p - 1) & " Estimation Report.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True

 

[John_w]

"DEBUG" isn't an error message.

You've omitted the "." before ActiveSheet - look carefully at my code.

If still no joy, post the full and exact error message and your complete code.

 

[francescovba]

• <section>Hello, I am exporting a file from Excel to PDF through a Macro. The file consists of a table of contents and several worksheets. The table of contents is hyperlinked to all the separate sheets in the document while on Excel format.
  When exporting to PDF however I lose the hyperlinks. Do you have any suggestions on how to solve this problem?
  I have bought Adobe Pro and have activated the “Add links” check box under the ACROBAT – preferences tab.
  
  here is my export PDF macro:
  
  Sub PRINTPDF_()
  '
  ' PRINT_ Macro
  ActiveWorkbook.Sheets.Select
  
  
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
  "Monthly Progress Report.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
  IgnorePrintAreas:=False, OpenAfterPublish:=True
  'Application.PrintCommunication = True
  'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
  'IgnorePrintAreas:=False
  End Sub
  
  </section>
  
  

 

[John_w]

• <section>Hello, I am exporting a file from Excel to PDF through a Macro. The file consists of a table of contents and several worksheets. The table of contents is hyperlinked to all the separate sheets in the document while on Excel format.
  When exporting to PDF however I lose the hyperlinks. Do you have any suggestions on how to solve this problem?
  I have bought Adobe Pro and have activated the “Add links” check box under the ACROBAT – preferences tab.
  </section>

Welcome to MrExcel, however could you start a new thread please, with an appropriate title.

I have written a VBA procedure which uses the Acrobat API (requires Adobe Pro to be installed) and which might work for you and I'll post it in your new thread.

 

[francescovba]

Hello John,

Please find attached the link with the new thread.

Thank you very much for your kind help.

https://www.mrexcel.com/forum/excel-questions/1053283-adding-links-pdf-excel-through-macro.html

 

[mohammadimran]

Hello everyone.

I am copying one value from a sheet and pasting in cell "E5" of the Print sheet tab.
This sheet has different vlookup formulas in different cells and pasting that value in E5 updates the value in other cells automatically.
Now I want to take a print/save pdf for this sheet and I need this copy from column B2 down to B96 and paste it in cell E5 of the sheet one by one and everytime it paste that value the print sheet is automatically saved with a new file name and the process continues untill all the 96 pages are saved as pdf with different values but same columns in it.
Your help will be really appreciated. I tried to do it via macro recorder however that is even painful than doing it all manually.

 

[John_w]

Hello everyone.

I am copying one value from a sheet and pasting in cell "E5" of the Print sheet tab.
This sheet has different vlookup formulas in different cells and pasting that value in E5 updates the value in other cells automatically.
Now I want to take a print/save pdf for this sheet and I need this copy from column B2 down to B96 and paste it in cell E5 of the sheet one by one and everytime it paste that value the print sheet is automatically saved with a new file name and the process continues untill all the 96 pages are saved as pdf with different values but same columns in it.
Your help will be really appreciated. I tried to do it via macro recorder however that is even painful than doing it all manually.

The code at https://www.mrexcel.com/forum/excel...nding-cell-value-post4883503.html#post4883503 should work for your case with a few changes: change the sheet names and cell ranges to your values.