Only print cells with values
 

I have a membership list that lists the church contributions made each week
and also gives the total cntributions for the year.
I want to print out a separate form for each member that has contributed
money during the year for income tax purposes
Thanks in advance

Only print cells with values
 

Here is some pseudo code

assume you have sheets

summarylist list of contributors, 1 per row
Detail weekly contributions 52 wks x member rows
form sheet to print out

You want to copy the up to 52 rows of data form detail to form for printout
for each paying member.


with worksheets("SummaryList")
set rng = .Range(.Cells(2,1),.Cells(2,1).End(xldown))
End With
for each cell in rng
' to see if has given this year - check column F
if cell.offset(0,5) 0 then
' Clear form sheet
Worksheets("Form").Range("A5:A56").EntireRow.Clear Contents
' use cell as a filter criteria on detail sheet
With worksheets("Detail").Range("A1").currentRegion
.Autofilter Field:=1, Criteria1:=cell.value
.rows.copy Worksheets("Form").Range("A5")
End With
with worksheets("form")
.range("B2").Value = cell
.range("J2").Value = Date
.printout
End with
end if
Next

--
Regards,
Tom Ogilvy


"Oldjay" wrote:

I have a membership list that lists the church contributions made each week
and also gives the total cntributions for the year.
I want to print out a separate form for each member that has contributed
money during the year for income tax purposes
Thanks in advance

Only print cells with values
 

You don't provide much information.
This short piece of code will print each row
in the selection. It will skip any blank rows.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub PrintEachRow()
'Jim Cone - San Francisco, USA - 06/30/2005
Dim rngPrint As Excel.Range
Dim rngRow As Excel.Range
Set rngPrint = Application.Intersect(Selection, ActiveSheet.UsedRange)

If Not rngPrint Is Nothing Then
For Each rngRow In rngPrint.Rows
If Application.CountA(rngRow) 0 Then
rngRow.PrintOut copies:=1
End If
Next
End If
Set rngPrint = Nothing
Set rngRow = Nothing
End Sub
'--------------

"Oldjay"

wrote in message
I have a membership list that lists the church contributions made each week
and also gives the total cntributions for the year.
I want to print out a separate form for each member that has contributed
money during the year for income tax purposes
Thanks in advance

Only print cells with values
 

"Tom Ogilvy" wrote:

Here is some pseudo code

assume you have sheets

summarylist list of contributors, 1 per row
Detail weekly contributions 52 wks x member rows
form sheet to print out

You want to copy the up to 52 rows of data form detail to form for printout
for each paying member.


with worksheets("SummaryList")
set rng = .Range(.Cells(2,1),.Cells(2,1).End(xldown))
End With
for each cell in rng
' to see if has given this year - check column F
if cell.offset(0,5) 0 then
' Clear form sheet
Worksheets("Form").Range("A5:A56").EntireRow.Clear Contents
' use cell as a filter criteria on detail sheet
With worksheets("Detail").Range("A1").currentRegion
.Autofilter Field:=1, Criteria1:=cell.value
.rows.copy Worksheets("Form").Range("A5")
End With
with worksheets("form")
.range("B2").Value = cell
.range("J2").Value = Date
.printout
End with
end if
Next

--
Regards,
Tom Ogilvy


"Oldjay" wrote:

I have a membership list that lists the church contributions made each week
and also gives the total cntributions for the year.
I want to print out a separate form for each member that has contributed
money during the year for income tax purposes
Thanks in advance