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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,643
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,643
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,089,337
Messages
5,407,672
Members
403,158
Latest member
Limerick2030

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top