Printing question vba

O

Ola Sigurdh

I have a workbook with several worksheets , when the workbook is ready for
printing I want to print out 1 page with 3 copies of three of the
worksheets and 1 to 10 pages with three copies of another sheet, depending
of how much data it is in that sheet. My question is. How to find out how
many pages that has data in it and then just print those pages. I have data
in columns A-G. I know how to record a macro to do the first thing and then
go to the next sheet and do the same thing again.

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=3
Sheets("Page2").Select

One more question. If I want to print out a worksheet if it contains data in
range B16:I46 and not print it if the range contains nothing how can I do
that in the first line of the macro above?

TIA

Ola Sigurdh
 
J

jeff

Hi,

For your 2nd question, try this macro.

jeff

Sub AnyData()
Dim r As Range
Set r = Range("B16:I46")
gg = 0
For Each c In r
If c.Value <> "" Then gg = -1
Next c

If gg = 0 Then ActiveWindow.SelectedSheets.PrintOut
From:=1, To:=1, Copies:=3

Sheets("Page2").Select

End Sub
 
J

jeff

Whoa, sorry this line was wrong - use

If gg <> 0 Then ActiveWindow.SelectedSheets.PrintOut

(working on Q #1....)

jeff
 
O

Ola Sigurdh

Worked lika a charm

Thank´s

Ola Sigurdh

"jeff" <[email protected]> skrev i meddelandet
Whoa, sorry this line was wrong - use

If gg <> 0 Then ActiveWindow.SelectedSheets.PrintOut

(working on Q #1....)

jeff
 
J

jeff

Ola,

As to your last question, I've put together a macro
(that you may have to test a bit and play with) which
I think checks each page (on screen) for data and
if there is none, won't print it. So, say you
have 10 pages to print, but 3 would be blank - this
skips those.

Try it out.
Jeff
--------------
Sub numPages()
Dim vloc(100), hloc(100) As Range
Dim vfull, hfull As Long
vfull = 0
hfull = 0
For Each pb In Worksheets(1).VPageBreaks
If pb.Extent = xlPageBreakFull Then
vfull = vfull + 1
Set vloc(vfull) = Worksheets(1).VPageBreaks
(vfull).Location
Else
cPartial = cPartial + 1
End If
Next pb
'MsgBox vfull & " V-full-screen page breaks, " & cPartial
& _
' " print-area page breaks at " & vloc(1).Address

For Each pb In Worksheets(1).HPageBreaks
If pb.Extent = xlPageBreakFull Then
hfull = hfull + 1
Set hloc(hfull) = Worksheets(1).HPageBreaks
(hfull).Location
Else
cPartial = cPartial + 1
End If
Next pb
'MsgBox hfull & " H-full-screen page breaks, " & cPartial
& _
' " print-area page breaks at " & hloc(1).Address

lastR = 1
lastC = 1
cols = vloc(1).Column - 1
Rws = hloc(1).Row - 1

For j = 1 To vfull + 1
For k = 1 To hfull + 1
pageNo = pageNo + 1
Dim r As Range
Set r = Range(Cells(lastR, lastC), Cells(Rws, cols))

gg = 0
For Each c In r
If c.Value <> "" Then gg = -1
Next c

If gg <> 0 Then ActiveWindow.SelectedSheets.PrintOut
From:=pageNo, To:=pageNo, Copies:=1 'or
test=MsgBox "Printing " & pageno


lastR = Rws + 1
Rws = Rws + Rws
Next k

If j = 1 Then
lastR = 1
lastC = cols + 1
If hloc(j).Row = 0 Then
Rws = Rws + Rws
Else
Rws = hloc(j).Row - 1
End If
Else
Rws = Rws + Rws
End If
cols = cols + cols
Next j

End Sub
 
Top