Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message oups.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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ups.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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ups.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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" 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" wrote in message ups.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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ups.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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing code | Excel Discussion (Misc queries) | |||
printing vba code in color | Excel Programming | |||
code for printing | Excel Programming | |||
Printing code | Excel Programming | |||
Improved printing of VBA code? | Excel Programming |