Access Beginner
Active Member
- Joined
- Nov 8, 2010
- Messages
- 311
- Office Version
- 2016
- Platform
- Windows
Hi everyone,
Below is my code to print to PDF a name range, which is based in
. The value in cell A55 has the name ranged.
The following
. 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.
Cheers
Haydn
Below is my code to print to PDF a name range, which is based in
VBA Code:
Set ToPrint = Sheets("Menu").Range("A55")"
The following
VBA Code:
Worksheets("Indigenous_Overview5").Activate
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: