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
:)

Copy rows with unique values to another sheet
 

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
:)

Copy rows with unique values to another sheet
 

how about datafilterautofiltercopy
or SORT

--
Don Guillett
SalesAid Software

"vmed" wrote in message
...
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
:)

Copy rows with unique values to another sheet
 

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

"somethinglikeant" wrote in message
ups.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
:)

Copy rows with unique values to another sheet
 

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
:)

Copy rows with unique values to another sheet
 

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
:)