Copy rows with unique values to another sheet

Thanking you in advance for any help. Using Excel 2003. I have a workbook
with 2 sheets. Sheet "A" has 4 culumns with unique data, about 400 rows. The
cells in column "A" of SHEET "A" contain data that starts with a letter then
is follow by numbers (i.e. N584452 or C325412). The second sheet (B) has just
a header. I have been trying to create a macro (macro novice) that will look
in a column ("A") of sheet A for a cell that starts with a certain letter
(i.e. "N"), then copy that complete row to the other sheet (B) starting in
row A2.

Thanks,
Vic

vic

not as short as i would like but this might be a good building block to
start with

Sub CopyOver()

Worksheets("B").Select: [A2].Select: qmark = ActiveCell.Address
qletter = "N"

Worksheets("A").Select: [A2].Select
Do Until IsEmpty(ActiveCell)
If UCase(Left(ActiveCell.Value, 1)) = qletter Then
Rows(ActiveCell.Row).Copy Sheets("B").Range(qmark)
Sheets("B").Select: ActiveCell.Offset(1, 0).Select: qmark =
ActiveCell.Address
Sheets("A").Select
End If
ActiveCell.Offset(1, 0).Select
Loop

End Sub

change the variable qletter to the letter you require to be copied

any probs give us a shout

http://www.excel-ant.co.uk




vmed wrote:
> Thanking you in advance for any help. Using Excel 2003. I have a workbook
> with 2 sheets. Sheet "A" has 4 culumns with unique data, about 400 rows. The
> cells in column "A" of SHEET "A" contain data that starts with a letter then
> is follow by numbers (i.e. N584452 or C325412). The second sheet (B) has just
> a header. I have been trying to create a macro (macro novice) that will look
> in a column ("A") of sheet A for a cell that starts with a certain letter
> (i.e. "N"), then copy that complete row to the other sheet (B) starting in
> row A2.
>
> Thanks,
> Vic
>

how about data>filter>autofilter>copy
or SORT

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"vmed" <vmed@discussions.microsoft.com> wrote in message
news:2E80B4A0-9CAA-4BC3-A06D-CE867D23FA88@microsoft.com...
> Thanking you in advance for any help. Using Excel 2003. I have a workbook
> with 2 sheets. Sheet "A" has 4 culumns with unique data, about 400 rows.
> The
> cells in column "A" of SHEET "A" contain data that starts with a letter
> then
> is follow by numbers (i.e. N584452 or C325412). The second sheet (B) has
> just
> a header. I have been trying to create a macro (macro novice) that will
> look
> in a column ("A") of sheet A for a cell that starts with a certain letter
> (i.e. "N"), then copy that complete row to the other sheet (B) starting in
> row A2.
>
> Thanks,
> Vic
>

except that it copies one at a time and selects when not necessary. If one
at a time

try something like this.

i=2
for each c in sheets("sourcesheet").range("a2:a"&
cells(rows.count,"a").end(xlup).row
if ucase(c)="N" then c.copy sheets("destsh").cells(i,"a")
i=i+1
next c



--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"somethinglikeant" <abmorgan@gmail.com> wrote in message
news:1154184939.336787.80510@b28g2000cwb.googlegroups.com...
> vic
>
> not as short as i would like but this might be a good building block to
> start with
>
> Sub CopyOver()
>
> Worksheets("B").Select: [A2].Select: qmark = ActiveCell.Address
> qletter = "N"
>
> Worksheets("A").Select: [A2].Select
> Do Until IsEmpty(ActiveCell)
> If UCase(Left(ActiveCell.Value, 1)) = qletter Then
> Rows(ActiveCell.Row).Copy Sheets("B").Range(qmark)
> Sheets("B").Select: ActiveCell.Offset(1, 0).Select: qmark =
> ActiveCell.Address
> Sheets("A").Select
> End If
> ActiveCell.Offset(1, 0).Select
> Loop
>
> End Sub
>
> change the variable qletter to the letter you require to be copied
>
> any probs give us a shout
>
> http://www.excel-ant.co.uk
>
>
>
>
> vmed wrote:
>> Thanking you in advance for any help. Using Excel 2003. I have a workbook
>> with 2 sheets. Sheet "A" has 4 culumns with unique data, about 400 rows.
>> The
>> cells in column "A" of SHEET "A" contain data that starts with a letter
>> then
>> is follow by numbers (i.e. N584452 or C325412). The second sheet (B) has
>> just
>> a header. I have been trying to create a macro (macro novice) that will
>> look
>> in a column ("A") of sheet A for a cell that starts with a certain letter
>> (i.e. "N"), then copy that complete row to the other sheet (B) starting
>> in
>> row A2.
>>
>> Thanks,
>> Vic
>>
>

somethinglikeant

That worked perfect.

Thanks,

Vic

"somethinglikeant" wrote:

> vic
>
> not as short as i would like but this might be a good building block to
> start with
>
> Sub CopyOver()
>
> Worksheets("B").Select: [A2].Select: qmark = ActiveCell.Address
> qletter = "N"
>
> Worksheets("A").Select: [A2].Select
> Do Until IsEmpty(ActiveCell)
> If UCase(Left(ActiveCell.Value, 1)) = qletter Then
> Rows(ActiveCell.Row).Copy Sheets("B").Range(qmark)
> Sheets("B").Select: ActiveCell.Offset(1, 0).Select: qmark =
> ActiveCell.Address
> Sheets("A").Select
> End If
> ActiveCell.Offset(1, 0).Select
> Loop
>
> End Sub
>
> change the variable qletter to the letter you require to be copied
>
> any probs give us a shout
>
> http://www.excel-ant.co.uk
>
>
>
>
> vmed wrote:
> > Thanking you in advance for any help. Using Excel 2003. I have a workbook
> > with 2 sheets. Sheet "A" has 4 culumns with unique data, about 400 rows. The
> > cells in column "A" of SHEET "A" contain data that starts with a letter then
> > is follow by numbers (i.e. N584452 or C325412). The second sheet (B) has just
> > a header. I have been trying to create a macro (macro novice) that will look
> > in a column ("A") of sheet A for a cell that starts with a certain letter
> > (i.e. "N"), then copy that complete row to the other sheet (B) starting in
> > row A2.
> >
> > Thanks,
> > Vic
> >
>
>

Vic,

I get an error when i ran the code gave by somethinglikeant. u replied
that it worked fine for you.

i created a book with two sheets named Sheet"A" and Sheet"B".. had
first row with entities like N45, Bghhh, N56 etc.. and then followed by
entities in other three columns.

the code din work. can u please let me know y?

BTW, is it Sheet"A" or just Sheet A. anyway, the code doent run for
both the cases.

please help

Expecting ur reply.
Thulasiram

vmed wrote:
> somethinglikeant
>
> That worked perfect.
>
> Thanks,
>
> Vic
>
> "somethinglikeant" wrote:
>
> > vic
> >
> > not as short as i would like but this might be a good building block to
> > start with
> >
> > Sub CopyOver()
> >
> > Worksheets("B").Select: [A2].Select: qmark = ActiveCell.Address
> > qletter = "N"
> >
> > Worksheets("A").Select: [A2].Select
> > Do Until IsEmpty(ActiveCell)
> > If UCase(Left(ActiveCell.Value, 1)) = qletter Then
> > Rows(ActiveCell.Row).Copy Sheets("B").Range(qmark)
> > Sheets("B").Select: ActiveCell.Offset(1, 0).Select: qmark =
> > ActiveCell.Address
> > Sheets("A").Select
> > End If
> > ActiveCell.Offset(1, 0).Select
> > Loop
> >
> > End Sub
> >
> > change the variable qletter to the letter you require to be copied
> >
> > any probs give us a shout
> >
> > http://www.excel-ant.co.uk
> >
> >
> >
> >
> > vmed wrote:
> > > Thanking you in advance for any help. Using Excel 2003. I have a workbook
> > > with 2 sheets. Sheet "A" has 4 culumns with unique data, about 400 rows. The
> > > cells in column "A" of SHEET "A" contain data that starts with a letter then
> > > is follow by numbers (i.e. N584452 or C325412). The second sheet (B) has just
> > > a header. I have been trying to create a macro (macro novice) that will look
> > > in a column ("A") of sheet A for a cell that starts with a certain letter
> > > (i.e. "N"), then copy that complete row to the other sheet (B) starting in
> > > row A2.
> > >
> > > Thanks,
> > > Vic
> > >
> >
> >