+ Reply to Thread
Results 1 to 7 of 7

Some help with Printing code

  1. #1
    Dean
    Guest

    Some help with Printing code

    I have a workbook with data feeding into a master page. The master page
    holds a maximum of 30 pages of information however my printing needs
    will vary from 1 page to the full 30 pages depending on how much data
    is entered.

    Each cell on the 30 pages has a value of zero until I input data. The
    print area has been set for the 30 pages.

    Currently when I select print it will print the full 30 pages but I
    want it to only print pages with a value other than zero.

    I have researched but am a little lost on how I can print those pages
    only with values higher than zero and would certainly value any
    assistance or guidance.

    Many Thanks,

    Dean


  2. #2
    Gary Keramidas
    Guest

    Re: Some help with Printing code

    somebody posted this and i adapted it a little. will it work for you? i used A1
    as the cell > 0

    Option Explicit

    Sub GroupSheets()

    Dim wks As Worksheet
    Dim Shts() As String
    Dim i As Integer
    i = 0
    For Each wks In ActiveWorkbook.Worksheets

    If wks.Range("A1").Value > 0 Then
    ReDim Preserve Shts(0 To i)
    Shts(i) = wks.Name
    Debug.Print wks.Name
    i = i + 1
    End If
    Next

    'select the array
    ActiveWorkbook.Worksheets(Shts).Select

    End Sub

    --


    Gary


    "Dean" <aus_legend@yahoo.com> wrote in message
    news:1142761489.602478.200520@e56g2000cwe.googlegroups.com...
    >I have a workbook with data feeding into a master page. The master page
    > holds a maximum of 30 pages of information however my printing needs
    > will vary from 1 page to the full 30 pages depending on how much data
    > is entered.
    >
    > Each cell on the 30 pages has a value of zero until I input data. The
    > print area has been set for the 30 pages.
    >
    > Currently when I select print it will print the full 30 pages but I
    > want it to only print pages with a value other than zero.
    >
    > I have researched but am a little lost on how I can print those pages
    > only with values higher than zero and would certainly value any
    > assistance or guidance.
    >
    > Many Thanks,
    >
    > Dean
    >




  3. #3
    Dean
    Guest

    Re: Some help with Printing code

    Gary,

    The code above highlighted both tabs my data page and master page but
    progressed no further.

    The code only needs to print off my master page.

    Appreciate your assistance.


  4. #4
    Tom Ogilvy
    Guest

    Re: Some help with Printing code

    Here is some pseudo code that might do what you want:

    Dim v as Variant, n as Long, i as Long
    Dim rng as Range
    v= Array("A1:D20,E1:H20, . . . 30 ranges")
    n = 0
    for i = lbound(v) to ubound(v)

    set rng = Range(v(i))
    if application.Sum(rng) = 0 then _
    exit for
    n = n + 1
    Next i
    Worsheets("Master").Printout from:=1, to:=n

    Adjust the array to refer to the appropriate 30 ranges that each would sum
    up to greater than 0 if you wanted to print up to that page

    --
    Regards,
    Tom Ogilvy

    "Dean" <aus_legend@yahoo.com> wrote in message
    news:1142769029.552827.10810@g10g2000cwb.googlegroups.com...
    > Gary,
    >
    > The code above highlighted both tabs my data page and master page but
    > progressed no further.
    >
    > The code only needs to print off my master page.
    >
    > Appreciate your assistance.
    >




  5. #5
    Jim Cone
    Guest

    Re: Some help with Printing code

    'Place in a standard module
    '-------------------------------
    Sub OnlyPrintNonZero()
    Dim rngArea As Excel.Range
    Dim rngRow As Excel.Range
    Dim strPrintArea As String

    Set rngArea = Worksheets("Master Page").UsedRange.Rows

    For Each rngRow In rngArea
    If Application.Sum(rngRow) > 0 Then
    strPrintArea = Range(rngArea(1), rngRow).Address
    Worksheets("Master Page").PageSetup.PrintArea = strPrintArea
    Exit For
    End If
    Next
    Set rngRow = Nothing
    Set rngArea = Nothing
    End Sub
    '---------
    'Place in ThisWorkbook module
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Call OnlyPrintNonZero
    End Sub
    '----------
    Jim Cone
    San Francisco, USA
    http://www.realezsites.com/bus/primitivesoftware



    "Dean" <aus_legend@yahoo.com> wrote in message news:1142769029.552827.10810@g10g2000cwb.googlegroups.com...
    Gary,

    The code above highlighted both tabs my data page and master page but
    progressed no further.

    The code only needs to print off my master page.

    Appreciate your assistance.


  6. #6
    Jim Cone
    Guest

    Re: Some help with Printing code

    And this might even work...
    '-----------------------------
    Sub OnlyPrintNonZero()
    'revised
    Dim rngArea As Excel.Range
    Dim lngRow As Long
    Dim lngCount As Long
    Dim strPrintArea As String

    Set rngArea = Worksheets("Master Page").UsedRange.Rows
    lngCount = rngArea.Rows.Count

    For lngRow = lngCount To 1 Step -1
    If Application.Sum(rngArea(lngRow)) > 0 Then
    strPrintArea = Range(rngArea(1), rngArea(lngRow)).Address
    Worksheets("Master Page").PageSetup.PrintArea = strPrintArea
    Exit For
    End If
    Next
    Set rngArea = Nothing
    End Sub
    '----------------------------
    Jim Cone


    "Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message...
    'Place in a standard module
    '-------------------------------
    Sub OnlyPrintNonZero()
    Dim rngArea As Excel.Range
    Dim rngRow As Excel.Range
    Dim strPrintArea As String

    Set rngArea = Worksheets("Master Page").UsedRange.Rows

    For Each rngRow In rngArea
    If Application.Sum(rngRow) > 0 Then
    strPrintArea = Range(rngArea(1), rngRow).Address
    Worksheets("Master Page").PageSetup.PrintArea = strPrintArea
    Exit For
    End If
    Next
    Set rngRow = Nothing
    Set rngArea = Nothing
    End Sub
    '---------
    'Place in ThisWorkbook module
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Call OnlyPrintNonZero
    End Sub
    '----------
    Jim Cone
    San Francisco, USA
    http://www.realezsites.com/bus/primitivesoftware



    "Dean" <aus_legend@yahoo.com> wrote in message news:1142769029.552827.10810@g10g2000cwb.googlegroups.com...
    Gary,
    The code above highlighted both tabs my data page and master page but
    progressed no further.
    The code only needs to print off my master page.
    Appreciate your assistance.


  7. #7
    Dave Peterson
    Guest

    Re: Some help with Printing code

    Just a potential typo problem:

    v= Array("A1:D20,E1:H20, . . . 30 ranges")
    should be:
    v= Array("A1:D20","E1:H20", . . . "30 ranges")




    Tom Ogilvy wrote:
    >
    > Here is some pseudo code that might do what you want:
    >
    > Dim v as Variant, n as Long, i as Long
    > Dim rng as Range
    > v= Array("A1:D20,E1:H20, . . . 30 ranges")
    > n = 0
    > for i = lbound(v) to ubound(v)
    >
    > set rng = Range(v(i))
    > if application.Sum(rng) = 0 then _
    > exit for
    > n = n + 1
    > Next i
    > Worsheets("Master").Printout from:=1, to:=n
    >
    > Adjust the array to refer to the appropriate 30 ranges that each would sum
    > up to greater than 0 if you wanted to print up to that page
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "Dean" <aus_legend@yahoo.com> wrote in message
    > news:1142769029.552827.10810@g10g2000cwb.googlegroups.com...
    > > Gary,
    > >
    > > The code above highlighted both tabs my data page and master page but
    > > progressed no further.
    > >
    > > The code only needs to print off my master page.
    > >
    > > Appreciate your assistance.
    > >


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1