VBA reference different sheets and range from a cell

Access Beginner

Active Member
Joined
Nov 8, 2010
Messages
311
Office Version
  1. 2016
Platform
  1. Windows
Hi everyone,

Below is my code to print to PDF a name range, which is based in
VBA Code:
 Set ToPrint = Sheets("Menu").Range("A55")"
. The value in cell A55 has the name ranged.

The following
VBA Code:
Worksheets("Indigenous_Overview5").Activate
. I'd like to replace this with the ability to reference different sheets based on a value in a cell. Say on the "Menu" sheet in cell A90. I would have a drop down list to choose from in this cell.

The reason I'm doing this via named ranges and values in a cell, is because if I make any changes to the VBA, any changes whatsoever, my company requires this changed to be signed off before I can use the code. This is obviously a pain and I'd like to avoid this wherever possible, hence the reason for using named ranges and cell values etc.

VBA Code:
Sub PDFPrint()

    Dim ReportPath As Variant
Dim ReportName As Variant
Dim ToPrint As Variant


Set ReportPath = Sheets("Menu").Range("A116")
Set ReportName = Sheets("Menu").Range("A52")
    Set ToPrint = Sheets("Menu").Range("A55")



Worksheets("Overview5").Activate
On Error Resume Next
With ActiveSheet
.PageSetup.PrintArea = ToPrint
.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=ReportPath & ReportName & ".PDF", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
On Error Resume Next

End With
MsgBox " PDF document:" & vbCrLf & "File Name: " _
& ReportName & vbCrLf & vbCrLf & " Has been created and has been saved to:" _
& vbCrLf & vbCrLf & "File Location: " & ReportPath
End Sub

Cheers
Haydn
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
You can use a reference to a cell for a sheet name or a range,
VBA Code:
Set ToPrint = Sheets(Sheets("Menu").Range("sheetname").Value).Range(Sheets("Menu").Range("namedrange").Value)
Where "sheetname" and "namedrange" refer to cells that hold the required name or range, you can use normal cell addresses, named ranges or table names.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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
Back
Top