Announcement

Collapse
No announcement yet.

VBA Code For Printing Variable Print Ranges

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Code For Printing Variable Print Ranges

    Hello,
    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!

    Jon

    Code:
    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
        
        ActiveWindow.SelectedSheets.PrintPreview
        
    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:

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

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

    Comment


    • #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?

      Comment


      • #4
        Re: Printing Variable Print Ranges

        Or http://www.ozgrid.com/forum/showthread.php?t=13668 with Custom Views

        Comment

        Working...
        X