Ron de Bruin has pretty much written the code for you and you can find it
at:

http://www.rondebruin.nl/copy5.htm

--
Regards,
Tom Ogilvy


"shark102" wrote in message
...
thanks for a prompt reply, but unfortunately it does not quite solve my
problem.
I am quite proficient with excell in general (not VBA yet) so at the
moment
I am filtering and copying these data manually.

The main point is that I will get this list every month to sort to
separate
worksheets, it has quite many 'heading3' descriptions and 'heading3'
description is not always gonna be the same I mean this month it is aaa,
bbb,
ccc
next month it might be xxx, yyy, zzz, so I am looking for an VBA code to
select all rows (+ headings) with the same heading 3 and copy it to new
worksheet then probably loop will run to jump to another heading 3
description.
I came up with sth like this:

x = ActiveCell.Row
y = x + 1
z = ActiveCell.Column

Do While Cells(y, z).Value < ""
If (Cells(x, z).Value = Cells(y, z).Value) Then
Cells(y, z).EntireRow.Activate
Else: y = y + 1
End If
Rows("1:" & y).Select
Selection.Copy
Sheets("Sheet1").Select
Sheets.Add
Range("A2").Select
ActiveSheet.Paste
y = y + 1
Loop

but it only copies headings and next 2 rows (should copy next 5 - I have
the
same heading 3 in 5 consecutive rows)

will work on this but any clue to help is greatly appreciated

PS I recorded macro and looked at the code but did not know how to make it
generic - code uses absolute reference to specific heading3 description, I
need it to do it by all heading 3 descriptions that exist on a list of
data
and are different every month.

"Norman Jones" wrote:

Hi Shark,

Look at using the Advanced Filter feature.

To copy filtered data to another sheet, it is necessary to invoke the
filter
from the target sheet.

If you ewant to automate this process, turn on the macro recorder and
then
perform the requisite steps manually. Th resultant code can be adapted
for
generic operation. If you require assistance with such adaptation, post
back
with details.

If you are not familiar with the Advanced filter feature, see Debra
Dalgleish's tutorial at:

http://www.contextures.com/xladvfilter01.html


---
Regards,
Norman



"shark102" wrote in message
...
hi

this is my first post so the question may seem naive but I only
recently
started dabbling in VBA. Ill try to be as clear as possible.

I have the following type of data

heading 1 heading 2 heading 3
456 43575 aaa
5854 65777 aaa
567 123 bbb
5678 3467 bbb
347 3657 bbb
3567 347 ccc

I need to copy headings and all rows with heading3 aaa to different
worksheet then copy headings with all rows with heading3 bbb to
different
worksheet etc.
data is already sorted by heading 3

thanks in advance