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]

Hi,
your ranges are unqualified which as you have discovered, means they will only apply to the ActiveSheet

See if this update to your code resolves your issue

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

Dave

 

[mohadin]

Something like

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

 

[mtagliaferri]

Hi,
your ranges are unqualified which as you have discovered, means they will only apply to the ActiveSheet

See if this update to your code resolves your issue

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

Dave

Hi,
Thanks for the code that solves the issue of running the code when I am on another sheet, but the file name created for the ShCompanyPropertyChecklist still refers to the cells of ShLeaversForm, how do I get the second form to reference to those cells (the range is the same for both sheets but the values are different).

 

[dmt32]

Hi,
Thanks for the code that solves the issue of running the code when I am on another sheet, but the file name created for the ShCompanyPropertyChecklist still refers to the cells of ShLeaversForm, how do I get the second form to reference to those cells (the range is the same for both sheets but the values are different).

Try this update

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

I have assumed that the worksheet object variables ShLeaversForm & ShCompanyPropertyChecklist have been assigned

Dave

 

[mtagliaferri]

Try this update

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

I have assumed that the worksheet object variables ShLeaversForm & ShCompanyPropertyChecklist have been assigned

Dave

Sorry Dave, I am doing a crash course on VBA and so far have very basic knowledge.... I don't have any variables set. I applied the code the above code and it seems to be working. Could you give me some hints of the variable you are referring to?

 

[dmt32]

Sorry Dave, I am doing a crash course on VBA and so far have very basic knowledge.... I don't have any variables set. I applied the code the above code and it seems to be working. Could you give me some hints of the variable you are referring to?

I am referring to these
ShLeaversForm & ShCompanyPropertyChecklist

I was not sure if you had declared them as object variables and set a reference to them

e.g.

Dim ShLeaversForm As Worksheet
Dim ShCompanyPropertyChecklist As Worksheet


Set ShLeaversForm = Worksheets("LeaversForm")
Set ShCompanyPropertyChecklist = Worksheets("CompanyPropertyChecklist")

or if they are sheet codenames?

Dave

 

[mtagliaferri]

I am referring to these
ShLeaversForm & ShCompanyPropertyChecklist

I was not sure if you had declared them as object variables and set a reference to them

e.g.

Dim ShLeaversForm As Worksheet
Dim ShCompanyPropertyChecklist As Worksheet


Set ShLeaversForm = Worksheets("LeaversForm")
Set ShCompanyPropertyChecklist = Worksheets("CompanyPropertyChecklist")

or if they are sheet codenames?

Dave

I just renamed the sheets in the VBA property, does that cause an issue?

 

[dmt32]

I just renamed the sheets in the VBA property, does that cause an issue?

not at all that is the sheets codename - perfectly ok to do that

Dave

 

[mtagliaferri]

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