Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 4 of 4

Thread: VBA Code For Printing Variable Print Ranges

  1. #1
    Join Date
    7th August 2006

    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

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    19th January 2007
    South Carolina, USA

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

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

  3. #3
    Join Date
    21st February 2006
    London, UK

    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?

    Excel Video Tutorials / Excel Dashboards Reports

  4. #4
    Join Date
    24th January 2003

    Re: Printing Variable Print Ranges

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Printing Named Ranges
    By Tiffany in forum EXCEL HELP
    Replies: 4
    Last Post: November 7th, 2005, 09:16
  2. Printing multiple ranges
    By dkr72 in forum EXCEL HELP
    Replies: 6
    Last Post: December 22nd, 2004, 22:01
  3. Replies: 10
    Last Post: March 10th, 2004, 01:36
  4. Replies: 8
    Last Post: November 26th, 2003, 07:06
  5. Printing: How to print 25 lines only per print sheet
    By sureshvellanki in forum EXCEL HELP
    Replies: 4
    Last Post: October 23rd, 2003, 21:39


Posting Permissions

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