Some help with Printing code

[Dean]

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

 

[Gary Keramidas]

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

 

[Dean]

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.

 

[Tom Ogilvy]

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("A120,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

 

[Jim Cone]

'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



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.

 

[Jim Cone]

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


'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



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]

Just a potential typo problem:

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