copying rows between sheets

[d. williams]

Hello. First, I'd like to thank all the participants on this group;
I've had several questions in the past and all were met with great speed
and helpful comments.

I have an excel file with four worksheets
["women","men","billings","mail"]. The first two sheets are set up so
that each row contains a persons' information, the first piece of which
is a unique id number (in the A column). I have a "billings" sheet
which contains numerous id numbers, also in the A column. What I need
to write is a macro which scrolls through the "billings" id numbers
(A2:A215) and compares that number to the A column of sheets "women" and
"men". If it finds the number, it should copy the entire row into the
blank sheed "mail". Thus at the end of the task, "mail" should contain
the information of all people who were listed on sheet "billing" who
were also found in "women" and "men".

I have very limited experience with Excel's VBA and while my first
priority is getting this task done, I would very much appreciate any
links to thorough books/web tutorials or well commented code I could use
to learn from.

Thank you for all your help. D.

 

[Frank Kabel]

Hi
as a starting point ahve a look at
http://www.rondebruin.nl/copy5.htm

For getting started with macros have a look at
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[d. williams]

update: i've been doing some testing, just using what i can figure out
of vb. however, i keep getting an error 424. also, i bet there are
better ways to do this. i could use some help with (a) getting the
error resolved and (b) getting the routine working, since i haven't been
able to test it yet. thanks. d.

-----
Sub test2()
Dim i As Integer, j As Integer
Dim count As Integer
count = 1
For i = 2 To 215
For j = 2 To 6693 'total no of women
If billings.Cells(i, 1).Value = women.Cells(j, 1).Value Then
women.Range("A" & j).EntireRow.Copy (mail.Rows(count))
count = count + 1
End If
Next j
Next i
End Sub