Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Dynamic Print area based on range typed into a cell

  1. #1
    New Member
    Join Date
    Feb 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Dynamic Print area based on range typed into a cell

    Is there a way that i can set the print area based on the INDIRECT function of a range typed into a cell.

    i have a worksheet with 52 sheets.
    i would like to run a Macro that runs through all the sheets and sets the Print area based on a cell in that sheet, then print that report
    Then run another macro that sets print area to a different area on that same sheet and then print that.

    1 report is all the pages, while other report is pages 1 and the last page. perhaps easier way to set to print all, but then a macro to print page 1 and last page of each sheet?

    Any help would be appreciated.

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,762
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Dynamic Print area based on range typed into a cell

    Quote Originally Posted by mbarbera83 View Post
    perhaps easier way to set to print all, but then a macro to print page 1 and last page of each sheet?
    Hi - you could try:

    Code:
    Sub m1()
    Dim ws As Worksheet, lp As Long
    For Each ws In ThisWorkbook.Worksheets 'Loop thru sheets and print all pages
        ws.PrintOut
    Next ws
    For Each ws In ThisWorkbook.Worksheets 'Loop thru sheets and print First & last page
        lp = (ws.HPageBreaks.Count + 1) * (ws.VPageBreaks.Count + 1)
        ws.PrintOut From:=1, To:=1
        If lp > 1 Then
            ws.PrintOut From:=lp, To:=lp
        End If
    Next ws
    End Sub
    [code]your code[/code]

  3. #3
    New Member
    Join Date
    Feb 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Print area based on range typed into a cell

    Hi - is there a way that I can customize this, that it prints all the pages to 1 PDF file .

    i am currently using the following code and "hard coding" the sheet names into the code when i want to select several sheets and print to 1 PDF.

    Application.ActivePrinter = "CutePDF Writer on CPW2:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "CutePDF Writer on CPW2:", Collate:=True

  4. #4
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,762
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Dynamic Print area based on range typed into a cell

    Hi, sounds like a completely different question? And the code does appear to have any sheet names hard coded?
    [code]your code[/code]

  5. #5
    New Member
    Join Date
    Feb 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Print area based on range typed into a cell

    my original plan was to create a macro that set print area on each sheet for Managers (pages 1 to 11 (11 could be 10 or 12, depending on the amount of info on that sheet) per sheet, with some managers receiving more than one sheet)
    and then to set another macro to set the print area on each sheet for Directors (pages 1 and the last page of each sheet, Directors to receive all sheets, but only 1st and last page per sheet)

    then asked a side question regarding perhaps just printing page 1 and last page per sheet, which you have answered, but ideally i would like one file for the directors.

    as for the hardcoding - here is an example of code used to print for 1 manager - printing 4 sheets
    Sheets(Array("2705", "2725", "2726", "2727")).Select
    Sheets("2705").Activate
    Application.ActivePrinter = "CutePDF Writer on CPW2:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "CutePDF Writer on CPW2:", Collate:=True
    Sheets("Summary").Select

  6. #6
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,762
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Dynamic Print area based on range typed into a cell

    So instead of the hard coding, how would we determine which sheets need to printed? If you want to manually select the sheets; remove these two lines, hold control and click on the sheets you want to print and run the macro.

    Code:
    Sheets(Array("2705", "2725", "2726", "2727")).Select
    Sheets("2705").Activate
    
    [code]your code[/code]

  7. #7
    New Member
    Join Date
    Feb 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Print area based on range typed into a cell

    i have created several macros that selects sheets per manager
    in that macro i then run the print to pdf macro

    the thing that i want to make dynamic, is the print area per sheet
    i would ideally like to have a cell on each sheet, where i would have to type in (or at a later stage have a formula) the desired print area - as this is dependant on certain variables, and varies per sheet. - i am prepared to do this manaully, as is it quicker than setting the print area bigger and then having to go to each sheet and then click and drag the print area page breaks as required.

    my though originally was to have a macro that sets the print area per an activated sheet based on an INDIRECT(B11) for example, but this did not seem to work.

  8. #8
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,762
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Dynamic Print area based on range typed into a cell

    So what cell on the sheet, what are the values can be in that cell and how do those values translate into the print area range?
    [code]your code[/code]

  9. #9
    New Member
    Join Date
    Feb 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Print area based on range typed into a cell

    i would like to have a macro that runs the following:

    ActiveSheet.PageSetup.PrintArea = "$E$5:$L$27,$Q$30:$X$44"

    ideally what i would like to do is in B11 of each sheet, to type in $E$5:$L$27,$Q$30:$X$44

    as on 2725 the print area is $E$5:$L$27,$Q$30:$X$44, while on
    2730 (a different sheet) $E$5:$L$55,$Q$30:$X$35

  10. #10
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,762
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Dynamic Print area based on range typed into a cell

    OK - give this a try, only very lightly tested:

    Code:
    Sub m2()
    Dim v, i As Long
    
    v = Array("2705", "2725", "2726", "2727")
    For i = 0 To UBound(v)
        Sheets(v(i)).PageSetup.PrintArea = Sheets(v(i)).Range("B11").Value
    Next i
    
    Application.ActivePrinter = "CutePDF Writer on CPW2:" 'Do you really need this line??
    Sheets(v).PrintOut Copies:=1, ActivePrinter:="CutePDF Writer on CPW2:", Collate:=True
    Sheets("Summary").Select
    
    End Sub
    [code]your code[/code]

Some videos you may like

User Tag List

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
  •