Referencing to specific worksheet

[mtagliaferri]

I recently posted a code and though was running perfectly till I figured out that I need to run the code when the user is on another sheet and therefore the reference to cell range and name don't tally...

What I am trying to achieve is that when I am oh the table sheet, I have a button that runs the below code:

Sub CreatePDF()
    ShLeaversForm.ExportAsFixedFormat xlTypePDF, Environ("Userprofile") & "\Documents\" & Range("D12") & " " & Range("D13") & " " & _
        Format(Range("E19"), "YYYYDDMM") & " " & ShLeaversForm.Name & ".pdf", OpenAfterPublish:=True
    ShCompanyPropertyChecklist.ExportAsFixedFormat xlTypePDF, Environ("Userprofile") & "\Documents\" & Range("D12") & " " & Range("D13") & " " & _
        Format(Range("E19"), "YYYYDDMM") & " " & ShCompanyPropertyChecklist.Name & ".pdf", OpenAfterPublish:=True

End Sub

This runs correctly only if I am running the code when I am on the ShLeaversForm and allocate the correct names, if I select the ShCompanyPropertyChecklist the code runs but the names given to the files are referred to the range of this sheet and therefore both names are wrong.

I realised that I have to reference the code to the specific sheet and wrote the below:

Sub CreatePDF()
    Worksheets("ShLeaversForm").ExportAsFixedFormat xlTypePDF, Environ("Userprofile") & "\Documents\" & Range("D12") & " " & Range("D13") & " " & _
        Format(Range("E19"), "YYYYDDMM") & " " & ShLeaversForm.Name & ".pdf", OpenAfterPublish:=True
    Worksheets("ShCompanyPropertyChecklist").ExportAsFixedFormat xlTypePDF, Environ("Userprofile") & "\Documents\" & Range("D12") & " " & Range("D13") & " " & _
        Format(Range("E19"), "YYYYDDMM") & " " & ShCompanyPropertyChecklist.Name & ".pdf", OpenAfterPublish:=True

End Sub

What is the correct code to reference it to the relative sheet?

 

[dmt32]

Thanks Dave for taking the time to solve my issue and answer my questions!!

most welcome - glad able to help & appreciate the feedback

Dave