I was rearranging your code within an email, so I guess I didn't get it
totally changed.

Glad you got it to work.

--
Regards,
Tom Ogilvy

"shark102" wrote in message
...
thanks a lot, it works, I modified it a bit to suit my needs and leave the
header for each set of data copied, I also deleted word 'then' in do while
condition which was causing syntax error

I love VBA!!!!, I hope soon I will be answering questions not asking them
:-)

code is below in case someone has similar problem

x = ActiveCell.Row
z = ActiveCell.Column
Do While ActiveCell < ""
y = x
Do While Cells(y, z).Value < "" And _
Cells(x, z).Value = Cells(y, z).Value '('then)

Rows("1:" & y).Select


y = y + 1
Loop

Selection.Copy
Sheets("Sheet1").Select
Sheets.Add
Range("A2").Select
ActiveSheet.Paste

Sheets("sheet1").Select

'next 4 lines select data previously copied but without header 'so that
'header is not deleted

Dim numberofrows
numberofrows = Selection.Rows.Count
Rows("5:" & numberofrows).Select
Selection.Delete

' Fix this line to always select your starting cell - depending where your
heading 'and data is, my data is in rows 5 onwards, headings in rows 1-4

Cells(5, z).Select
Loop



"Tom Ogilvy" wrote:

It is hard to work with code that is based on whatever cell is active,
so
this is pseudo code

x = ActiveCell.Row
z = ActiveCell.Column
Do while ActiveCell < ""
y = x
Do While Cells(y, z).Value < "" and _
Cells(x, z).Value = Cells(y, z).Value Then
Rows("1:" & y).Select
y = y + 1
Loop

Selection.Copy
Sheets("Sheet1").Select
Sheets.Add
Range("A2").Select
ActiveSheet.Paste

Sheets("sheet1").Select
Selection.Delete
' Fix this line to always select your starting cell
cells(1,z).Select
Loop

--
Regards,
Tom Ogilvy



"shark102" wrote in message
...
sorry for being pain in the neck, but this code is too advanced and
I'd
like
to get something I would understand.
Anyway I came up with sth, the code is below:

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
Rows("1:" & y).Select

Else: y = y + 1

End If

y = y + 1
Loop

Selection.Copy
Sheets("Sheet1").Select
Sheets.Add
Range("A2").Select
ActiveSheet.Paste

Sheets("sheet1").Select
Dim numberofrows
numberofrows = Selection.Rows.Count
Rows("5:" & numberofrows).Select
Selection.Delete

it does the first part of the job, meaning it copies first set of data
with
the same description (including heading) and then deletes it but
leaves
the
heading.
now I need to loop it so that it comes back and copies next set of
data
etc.
I tried different combinations but it work for first set with the same
criteria but then goes weird ways.

my headings are in rows 1-4

spent whole afternoon on it but beginnings are hard I guess

"Tom Ogilvy" wrote:

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