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!

 

[Peter_SSs]

Try

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

BTW, which sheet is the A63 with the pdf name in?

 

[Jyggalag]

Try

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

Thank you Peter!

I am still very inexperienced in VBA and am a bit scared to make any harsh edits, if you do not mind, do I replace this piece with code already there, or do I insert it on top of it (and if so, where?).

Kind regards,
Jyggalag

 

[Peter_SSs]

Take out the red line and insert the blue one

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"
Sheets(1).ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & fn & ".pdf"
End Sub

 

[Jyggalag]

Take out the red line and insert the blue one

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"
Sheets(1).ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & fn & ".pdf"
End Sub

Thank You Peter!

I am currently getting this debug error however:

Is it because my sheet is named Sheet3 and maybe I need to mention this somehow?

 

[Peter_SSs]

Is it because my sheet is named Sheet3 and maybe I need to mention this somehow?

It shouldn't be, but see further questions below.

What is the answer to my earlier question?

BTW, which sheet is the A63 with the pdf name in?

Also,
- which sheet is the active sheet when the code is run?
- does your workbook have any hidden sheets?

 

[Jyggalag]

H

It shouldn't be, but see further questions below.

What is the answer to my earlier question?


Also,
- which sheet is the active sheet when the code is run?
- does your workbook have any hidden sheets?

Hi Peter,

Thanks for your quick response.

The cell A63 is also in Sheet3 (apologies for not mentioning this before) and the code is run through a macro button in Sheet3 as well.

I have no hidden sheets. So only the four sheets shown in the first photo

Hope this makes sense?

 

[Peter_SSs]

Then the most likely problem is either a value in cell A63 that is not a valid file name or a file path that does not exist.

 

[Peter_SSs]

the code is run through a macro button in Sheet3 as well.

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"

 

[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"

I do not think there is an issue with the name in cell A63, it worked before and was able to save the file (although with every sheet in the excel file).

I replaced it now, but it still debugs and highlights this:

Did I perhaps misunderstand something?

Thanks again