saving as pdf with changing additional sheet name

[spamiki]

Hi,

I have a problem with VBA, I have a macro to save active excel sheet as PDF with custom name which is generated in cell.
Now I need to change it to have the same formula, but there have to additionally added to PDF additional excel sheet from the same file where sheet name will be generated in cell Z22 (in file there are 22 sheets, depend of chosed variant in active sheet, additional one generated in cell Z22 have to be also added and saved in one PDF)

Current code:

Sub ExportPDF()
With ActiveSheet
.ExportAsFixedFormat 0, Environ("USERPROFILE") & "\Desktop\" & _
.Range("B84").Value & ".pdf", OpenAfterPublish:=True
End With
End Sub

Guys, could you help me please?
Main active excel sheet named is "Report"

Thank you!

 

[John_w]

Try this:

Public Sub Save_2_Sheets_As_PDF()

    With ActiveSheet
        Worksheets(Array(.Name, .Range("Z22").Value)).Select
        .Activate
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=Environ("USERPROFILE") & "\Desktop\" & .Range("B84").Value & ".pdf", OpenAfterPublish:=True
        .Select
    End With
    
End Sub