Dynamic print range VBA

[Giulianeo]

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]

I am not sure I understand the question but maybe this:

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]

I am not sure I understand the question but maybe this:

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]

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.