Dynamic print range VBA

Giulianeo

New Member
Joined
Sep 26, 2019
Messages
11
Hey there,

I am trying to create a macro that will "read" the range from a cell such as "02" (A1:M39) and export that range as a PDF, however I have only been able to set the range directly in the VBA code, is it even possible to have it read a range that changes dynamically?

For comparison sake you can have excel read values of cells such as .To Range ("M1") and it will return the value of the cell....

Thanks!

Sub Testexport()

'Export PDF Wall certificate / Close certificate workbook.

Sheets(Array("Certificate")).Select
Range("A1:M39").ExportAsFixedFormat , Type:=xlTypePDF, Filename:="V:\DEPARTMENTS\DRIVER TRAINING\COORDINATION\Scoresheets\Wall Certificate Macro\Digital Wall Certificate", openafterpublish:=False, ignoreprintareas:=False
Workbooks("Digital Wall Certificate.xlsx").Close SaveChanges:=False


End Sub
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,403
Office Version
2013
Platform
Windows
I am not sure I understand the question but maybe this:
Code:
Sub Testexport()
'Export PDF Wall certificate / Close certificate workbook.
Dim rng As String
rng = Range("Z1").Value
Sheets(Array("Certificate")).Select
Range(rng).ExportAsFixedFormat , Type:=xlTypePDF, Filename:="V:\DEPARTMENTS\DRIVER TRAINING\COORDINATION\Scoresheets\Wall Certificate Macro\Digital Wall Certificate", openafterpublish:=False, ignoreprintareas:=False
Workbooks("Digital Wall Certificate.xlsx").Close SaveChanges:=False
End Sub
Where cell Z1 would contain the 'A1:M39' text, without quote marks. You can change cell Z1 value to any range address you want to export without having to change the code.
 

Giulianeo

New Member
Joined
Sep 26, 2019
Messages
11
I am not sure I understand the question but maybe this:
Code:
Sub Testexport()
'Export PDF Wall certificate / Close certificate workbook.
Dim rng As String
rng = Range("Z1").Value
Sheets(Array("Certificate")).Select
Range(rng).ExportAsFixedFormat , Type:=xlTypePDF, Filename:="V:\DEPARTMENTS\DRIVER TRAINING\COORDINATION\Scoresheets\Wall Certificate Macro\Digital Wall Certificate", openafterpublish:=False, ignoreprintareas:=False
Workbooks("Digital Wall Certificate.xlsx").Close SaveChanges:=False
End Sub
Where cell Z1 would contain the 'A1:M39' text, without quote marks. You can change cell Z1 value to any range address you want to export without having to change the code.

Thank you for your answer, I tried your code but it gives me a argument not optional compile error.

I apologize, it is a little complicated to explain, basically I have all this completion certificates in an excel sheet, they are images with text boxes over them... the problem is there are 20 in one sheet and if I only have 5 students per say, it exports a PDF file with the first 5 certificates filled with their names but the remaining 15 are empty but still visible which looks unprofessional, everything works with a bigger macro that does a bunch of stuff together and emails them automatically as an attachment... The final piece of the puzzle if figuring out a way for excel to only export the certificates that have student names on them... for reference the text boxes have a = formula so they read data from a second sheet... hopefully I explained a little better what I am trying to do..

Thank you!!!
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,403
Office Version
2013
Platform
Windows
Code:
Range(rng).ExportAsFixedFormat , Type:=xlTypePDF, Filename:="V:\DEPARTMENTS\DRIVER TRAINING\COORDINATION\Scoresheets\Wall Certificate
The comma after ExportAsFixedFormat is causing the 'Argument not optional' message. But I had a different problem in that it would not same the file as PDF. I don't have a V: drive.
 
Last edited:

Forum statistics

Threads
1,084,933
Messages
5,380,652
Members
401,695
Latest member
dwoychowski

Some videos you may like

This Week's Hot Topics

Top