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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,518
Office Version
  1. 2019
Platform
  1. Windows
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

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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Something like
VBA Code:
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

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

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

Well-known Member
Joined
Jul 3, 2012
Messages
6,518
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

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

Board Regular
Joined
Oct 27, 2004
Messages
143
Try this update

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

Well-known Member
Joined
Jul 3, 2012
Messages
6,518
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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.
VBA Code:
Dim ShLeaversForm As Worksheet
Dim ShCompanyPropertyChecklist As Worksheet


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

or if they are sheet codenames?

Dave
 
Solution

mtagliaferri

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

Attachments

  • Capture.JPG
    Capture.JPG
    25.7 KB · Views: 1

Watch MrExcel Video

Forum statistics

Threads
1,129,927
Messages
5,639,035
Members
417,065
Latest member
ALONSO_1150

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