selecting range and copying to new worksheet

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" <shark102@discussions.microsoft.com> wrote in message
news:8E2760F5-50A6-4928-9C8B-ADF71B46BDB9@microsoft.com...
> 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" <shark102@discussions.microsoft.com> wrote in message
> news:8E2760F5-50A6-4928-9C8B-ADF71B46BDB9@microsoft.com...
> > 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" <shark102@discussions.microsoft.com> wrote in message
news:BB5E885D-F913-4F85-A55E-7536BC223E5E@microsoft.com...
> 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" <shark102@discussions.microsoft.com> wrote in message
> > news:8E2760F5-50A6-4928-9C8B-ADF71B46BDB9@microsoft.com...
> > > 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" <shark102@discussions.microsoft.com> wrote in message
> news:BB5E885D-F913-4F85-A55E-7536BC223E5E@microsoft.com...
> > 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" <shark102@discussions.microsoft.com> wrote in message
> > > news:8E2760F5-50A6-4928-9C8B-ADF71B46BDB9@microsoft.com...
> > > > 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" <shark102@discussions.microsoft.com> wrote in message
news:47EEAE2F-9FB6-4CEC-A38A-B736038A3A61@microsoft.com...
> 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" <shark102@discussions.microsoft.com> wrote in message
> > news:BB5E885D-F913-4F85-A55E-7536BC223E5E@microsoft.com...
> > > 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" <shark102@discussions.microsoft.com> wrote in message
> > > > news:8E2760F5-50A6-4928-9C8B-ADF71B46BDB9@microsoft.com...
> > > > > 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 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" <shark102@discussions.microsoft.com> wrote in message
> news:47EEAE2F-9FB6-4CEC-A38A-B736038A3A61@microsoft.com...
> > 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" <shark102@discussions.microsoft.com> wrote in message
> > > news:BB5E885D-F913-4F85-A55E-7536BC223E5E@microsoft.com...
> > > > 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" <shark102@discussions.microsoft.com> wrote in message
> > > > > news:8E2760F5-50A6-4928-9C8B-ADF71B46BDB9@microsoft.com...
> > > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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" <shark102@discussions.microsoft.com> wrote in message
news:DE97881B-A0BC-46EC-AA9A-07148320A43E@microsoft.com...
> 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" <shark102@discussions.microsoft.com> wrote in message
> > news:47EEAE2F-9FB6-4CEC-A38A-B736038A3A61@microsoft.com...
> > > 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" <shark102@discussions.microsoft.com> wrote in message
> > > > news:BB5E885D-F913-4F85-A55E-7536BC223E5E@microsoft.com...
> > > > > 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" <shark102@discussions.microsoft.com> wrote in message
> > > > > > news:8E2760F5-50A6-4928-9C8B-ADF71B46BDB9@microsoft.com...
> > > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >