No announcement yet.

VBA Code For Printing Variable Print Ranges

  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Code For Printing Variable Print Ranges

    I am trying to write VBA code that will print a print range that is presented in cell F3 on a "Reports" worksheet. The content of F3 will change depending on how many reports the user selects to print. For example, he could select one, two, three reports etc - up to twelve. The cell ranges of each report are named (e.g. Report1, Report2 etc) so that if the user selects to print Reports 1 and 2, the contents of cell F3 are "Report1,Report2".

    If I replace WhatToPrint with "Report1,Report2" the print macro works. Can anyone help me to understand why it doesn't work when I leave WhatToPrint in?

    Really appreciate your help with this!


    Sub Macro2()
    ' Macro2 Macro
    ' Macro recorded 05/05/2009 by JW8836
        Dim WhatToPrint As String
        WhatToPrint = Sheets("Reports").Cells(3, 6).Value
        'sets the variable to equal the contents of cell D3 which contains the formula
        'summarising the print ranges I want to print
        Sheets("Reports").Cells(3, 6).Select
        ActiveCell.FormulaR1C1 = WhatToPrint
        ' pastes the variable in cell F3 - just to check that it looks like I want it to
        Sheets("Reports").PageSetup.PrintArea = WhatToPrint
        'uses the variable to set print area - this is where it fails!
        'if you replace the variable with the contents of cell F3 the macro will work
    End Sub

  • #2
    Re: Printing Variable Print Ranges

    Define each print area as a named range.
    Use Data Validation, list option, to present the report selections
    Pass the value in the validation cell to VBA as follows:

    Sub PrintReport()
        With Sheet1
            .PageSetup.PrintArea = "" 'clear the print area
            .PageSetup.PrintArea = .Range("A1").Value
        End With
    End Sub
    See attached.
    Attached Files

    Forum Rules | Message to Cross Posters | How to use Tags


    • #3
      Re: Printing Variable Print Ranges

      Your code works for me, though I don't see why you need the second and third lines. What error do you get?


      • #4
        Re: Printing Variable Print Ranges

        Or with Custom Views