Results 1 to 4 of 4

Thread: Dynamic print range VBA

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Dynamic print range VBA

    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

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,062
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Dynamic print range VBA

    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.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic print range VBA

    Quote Originally Posted by JLGWhiz View Post
    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!!!

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,062
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Dynamic print range VBA

    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 by JLGWhiz; Sep 26th, 2019 at 06:38 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •