Referencing to specific worksheet

mtagliaferri

Board Regular
Joined
Oct 27, 2004
Messages
143
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:

VBA 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:

VBA Code:
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?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Watch MrExcel Video

Forum statistics

Threads
1,129,931
Messages
5,639,061
Members
417,067
Latest member
rohitbabshet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top