Vba - pdf a non active sheet

[Viperuk]

Hi all

I have a wb with a selection of sheets.

What I am trying to do is write code so I can pdf a specific sheet from a front page.

My idea is you select the first sheet and then press the button for the sheet you wish to pdf. I don't want the pdf button on every sheet.

I can get vba to pdf the active sheet, but I'm struggling to pdf a non active sheet from the active sheet.

Any advice will be appreciated.

 

[Yongle]

Sub ExportToPDFs()

Dim ws As Worksheet

[COLOR=#006400][I]'a loop[/I][/COLOR]
For Each ws In Worksheets
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Test\" & ws.Name & ".pdf"
Next ws
[COLOR=#006400][I]    
'single sheet[/I][/COLOR]
    Sheets("SheetName").ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Test\" & "MyPDFname" & ".pdf"
[COLOR=#006400] 'or[/COLOR]
    Set ws = Sheets("SheetName")
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Test\" & "MyPDFname" & ".pdf"
End Sub

 

[Viperuk]

Thank you yongle

This is working, the problem I'm. Having is now with the file save name which are referenced to the same range in each sheet.

How can I specify the sheet required for the file name.

I will try and post the code

 

[Viperuk]

I've managed to figure it out

Many thanks

 

[Yongle]

Thanks for feedback