Form Printing

  • Thread starter Thread starter Suzanne
  • Start date Start date
S

Suzanne

I'm looking for a method (to be associated with a command button) that will
print an entire form page, but only if there is data in the top section of
the form (Hazard Form).

Specifically

A1:Q67 will always print
If G71 <> "", print A68:Q130
If G134 <> "", print A131:Q192
and so on

Note: G71, G134, etc. have formulas that pull data from another worksheet

Thanks -- Suzanne
 
Did you want to print each of these ranges on a separate sheet or did you expect
to print these ranges as a continuous range?
 
The document is already formatted... the form sections in the ranges below
fill one page
 
Option Explicit
Private Sub CommandButton1_Click()

Dim RngToPrint As Range
Dim rCtr As Long
Dim LastRow As Long
Dim FoundSomething As Boolean
Dim Resp As Long
Dim myStep As Long

myStep = 62

FoundSomething = False
With Me
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
Set RngToPrint = .Range("a1:Q67")
For rCtr = 68 To LastRow Step myStep
If .Cells(rCtr + 3, "A").Value = "" Then
'skip this section
Else
FoundSomething = True
Set RngToPrint _
= Union(RngToPrint, .Cells(rCtr, "A").Resize(myStep, 17))
End If
Next rCtr
End With

Resp = vbYes
If FoundSomething = False Then
Resp = MsgBox(Prompt:="Only headers will be printed" _
& vbLf & "Continue?", Buttons:=vbYesNo)
End If

If Resp = vbYes Then
RngToPrint.PrintOut preview:=True 'for testing
End If

End Sub
 
Back
Top