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

Tom Oqilvy wrote
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

I wrote but for some reason it did not post

"I should have given more info

I have a sheet (Records) that has a range "Database" that has in col A
Member#, col B the last name of the member, C with the first name, Col E
Address1, F city, G State, H zip and other cols thru M with other info.
Infomation is entered thru a user form.

I have a sheet "Contributions" which I import "Dasebase" then hide Col A and
Col lI thru M
In col N I have the formula =IF(SUM(O3:BN3)=0,"",SUM(O3:BN3))
Col O thru BN have the 52 weeke in the year
In Sheet "TaxForm" I have
cell B3 for the FirstName,
cell B4 for the LastName,
cell B5 for the Address1
cell B6 for the City,
cell B7 for the State,
cell B8 for the zip
cell B9 for the Total Contributions
Again I wish to print out a form for each menber that gave a contribution
during the year

oldjay

sub PrintoutTax()
Dim sh as Wroksheet, sh1 as Worksheet
Dim rw as Long
set sh1 = Worksheets("TaxForm")
set sh = worksheets("Contributions")
with sh
rw = 3
do while sh.cells(rw,1)< ""
if sh.cells(rw,"N") < "" then
sh1.Range("B3").Value = sh.Cells(rw,"C").Value
sh1.Range("B4").Value = sh.Cells(rw,"B").Value
sh.cells(rw,"E").Resize(1,4).copy
sh1.Range("B5").PasteSpecial xlvalues, transpose:=True
sh1.Range("B9").Value = sh.Cells(rw,"N").Value
sh1.Printout
end if
rw = rw + 1
Loop
End sub

--
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

Tom Oqilvy wrote
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

I wrote but for some reason it did not post

"I should have given more info

I have a sheet (Records) that has a range "Database" that has in col A
Member#, col B the last name of the member, C with the first name, Col E
Address1, F city, G State, H zip and other cols thru M with other info.
Infomation is entered thru a user form.

I have a sheet "Contributions" which I import "Dasebase" then hide Col A and
Col lI thru M
In col N I have the formula =IF(SUM(O3:BN3)=0,"",SUM(O3:BN3))
Col O thru BN have the 52 weeke in the year
In Sheet "TaxForm" I have
cell B3 for the FirstName,
cell B4 for the LastName,
cell B5 for the Address1
cell B6 for the City,
cell B7 for the State,
cell B8 for the zip
cell B9 for the Total Contributions
Again I wish to print out a form for each menber that gave a contribution
during the year

oldjay

Thanks Tom It got me pointed in the right direction
oldjay
PS- Minor corrections You had worksheet spelled wromg and you forgot End
With


"Tom Ogilvy" wrote:

sub PrintoutTax()
Dim sh as Wroksheet, sh1 as Worksheet
Dim rw as Long
set sh1 = Worksheets("TaxForm")
set sh = worksheets("Contributions")
with sh
rw = 3
do while sh.cells(rw,1)< ""
if sh.cells(rw,"N") < "" then
sh1.Range("B3").Value = sh.Cells(rw,"C").Value
sh1.Range("B4").Value = sh.Cells(rw,"B").Value
sh.cells(rw,"E").Resize(1,4).copy
sh1.Range("B5").PasteSpecial xlvalues, transpose:=True
sh1.Range("B9").Value = sh.Cells(rw,"N").Value
sh1.Printout
end if
rw = rw + 1
Loop
End sub

--
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

Tom Oqilvy wrote
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

I wrote but for some reason it did not post

"I should have given more info

I have a sheet (Records) that has a range "Database" that has in col A
Member#, col B the last name of the member, C with the first name, Col E
Address1, F city, G State, H zip and other cols thru M with other info.
Infomation is entered thru a user form.

I have a sheet "Contributions" which I import "Dasebase" then hide Col A and
Col lI thru M
In col N I have the formula =IF(SUM(O3:BN3)=0,"",SUM(O3:BN3))
Col O thru BN have the 52 weeke in the year
In Sheet "TaxForm" I have
cell B3 for the FirstName,
cell B4 for the LastName,
cell B5 for the Address1
cell B6 for the City,
cell B7 for the State,
cell B8 for the zip
cell B9 for the Total Contributions
Again I wish to print out a form for each menber that gave a contribution
during the year

oldjay

Actually With Sh isn't need. - so I must have forgotten to delete it and I
admit, I mispelled worksheet.

--
Regards,
Tom Ogilvy


"Oldjay" wrote:

Thanks Tom It got me pointed in the right direction
oldjay
PS- Minor corrections You had worksheet spelled wromg and you forgot End
With


"Tom Ogilvy" wrote:

sub PrintoutTax()
Dim sh as Wroksheet, sh1 as Worksheet
Dim rw as Long
set sh1 = Worksheets("TaxForm")
set sh = worksheets("Contributions")
with sh
rw = 3
do while sh.cells(rw,1)< ""
if sh.cells(rw,"N") < "" then
sh1.Range("B3").Value = sh.Cells(rw,"C").Value
sh1.Range("B4").Value = sh.Cells(rw,"B").Value
sh.cells(rw,"E").Resize(1,4).copy
sh1.Range("B5").PasteSpecial xlvalues, transpose:=True
sh1.Range("B9").Value = sh.Cells(rw,"N").Value
sh1.Printout
end if
rw = rw + 1
Loop
End sub

--
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

Tom Oqilvy wrote
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

I wrote but for some reason it did not post

"I should have given more info

I have a sheet (Records) that has a range "Database" that has in col A
Member#, col B the last name of the member, C with the first name, Col E
Address1, F city, G State, H zip and other cols thru M with other info.
Infomation is entered thru a user form.

I have a sheet "Contributions" which I import "Dasebase" then hide Col A and
Col lI thru M
In col N I have the formula =IF(SUM(O3:BN3)=0,"",SUM(O3:BN3))
Col O thru BN have the 52 weeke in the year
In Sheet "TaxForm" I have
cell B3 for the FirstName,
cell B4 for the LastName,
cell B5 for the Address1
cell B6 for the City,
cell B7 for the State,
cell B8 for the zip
cell B9 for the Total Contributions
Again I wish to print out a form for each menber that gave a contribution
during the year

oldjay