Save excel sheet as PDF (only one sheet)

[Jyggalag]

Hi all,

I currently have a macro that converts my excel sheet into a PDF and saves it in a designated folder. However, I only want it to save the first sheet as a PDF, and ignore my other sheets, which it will not do at the moment.

My code looks as follows:

Option Explicit

Sub SaveFileWithMacro()

Dim Path As String
Dim fn As String
Path = "S:\PATH\Tracking\PDF files\"
fn = Range("A63")
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=Path & fn & ".pdf"

End Sub

A63 is the cell in which I type in the name of the PDF file to be saved.

Can anybody help me fix my macro so it only saves the first sheet in the below picture (the one called "Sheet3")?

Thank you!

 

[Jyggalag]

In that case you should also be able to swap these lines (though the red one should still work if the sheet is definitely the first sheet in the workbook).

Sheets(1).ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & fn & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & fn & ".pdf"

This is the debug error btw:

 

[Jyggalag]

In that case you should also be able to swap these lines (though the red one should still work if the sheet is definitely the first sheet in the workbook).

Sheets(1).ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & fn & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & fn & ".pdf"

Dear Peter,

Apologies for my sillyness. It turns out that it was unable to save the file because I had it in open in PDF in my background.

Embarrassing mistake, but your formula works as intended now!

Very much appreciated and thank you SO much for your help!

If I may ask one last question, if I were to move this code to another sheet (so the macro button is not in the sheet for the pdf that I want printed) and let's say this other sheet is called Sheet2, how should I change the macro so it still works as intended and publishes Sheet3 (as before) as a PDF and saves it in my folder?

Thank you very much!

 

[Peter_SSs]

n, if I were to move this code to another sheet (so the macro button is not in the sheet for the pdf that I want printed) and let's say this other sheet is called Sheet2, how should I change the macro so it still works as intended and publishes Sheet3 (as before) as a PDF and saves it in my folder?

What is most important? ..
- That the sheet to be converted to pdf is called 'Sheet3', or
- That the sheet to be converted to pdf is the left hand sheet in the workbook?

 

[Jyggalag]

What is most important? ..
- That the sheet to be converted to pdf is called 'Sheet3', or
- That the sheet to be converted to pdf is the left hand sheet in the workbook?

Both would be fine!

Sheet3 seems more flexible just in case of future events, but if it's too difficult, I definitely do not mind the other solution

 

[Peter_SSs]

Sheet3 seems more flexible

Try this then

Sub SaveFileWithMacro_v2()
  Dim Path As String
  Dim fn As String
  
  Path = "S:\PATH\Tracking\PDF files\"
  With Sheets("Sheet3")
    fn = .Range("A63")
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & fn & ".pdf"
  End With
End Sub