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

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

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

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

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

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