Afternoon All,
Had a look in discussions cant find what I am looking for.

Is it possible to use a macro to look for a certain portion of text in this
case "SC"
in a cell and copy the entire row to a new sheet.

Raw data is just over 2000 rows and dont want to copy and paste all
individually.

Have tried sorting the data, but due to other characters in that particular
cell cant get all SC rows together.
TIA
Ajay

You could do it in a macro
or
in a helper column
=iserror(find("SC",A1))
copy to the end of the data
use auto filter on this coulumn to find all the "False"
copy these rows and paste into the new worksheet

if you have to do it a lot, write the macro.
If you only have to do it once in a blue moon do it by hand.

"Ajay" wrote:

Afternoon All,
Had a look in discussions cant find what I am looking for.

Is it possible to use a macro to look for a certain portion of text in this
case "SC"
in a cell and copy the entire row to a new sheet.

Raw data is just over 2000 rows and dont want to copy and paste all
individually.

Have tried sorting the data, but due to other characters in that particular
cell cant get all SC rows together.
TIA
Ajay

Cheers bj
Tried this out and get all TRUE values. Probably doing something wrong.
Examples of values in target cells:
12562/SC
PRISMATICS/12531/SC
VALVES/12521/SC
FAI/SC
I entered =iserror(find("*SC*",E1)) is this incorrect?
It looks as if I will be doing this alot so any help with a macro would be
great
Thanks for your help
ajay

"bj" wrote:

You could do it in a macro
or
in a helper column
=iserror(find("SC",A1))
copy to the end of the data
use auto filter on this coulumn to find all the "False"
copy these rows and paste into the new worksheet

if you have to do it a lot, write the macro.
If you only have to do it once in a blue moon do it by hand.

"Ajay" wrote:

Afternoon All,
Had a look in discussions cant find what I am looking for.

Is it possible to use a macro to look for a certain portion of text in this
case "SC"
in a cell and copy the entire row to a new sheet.

Raw data is just over 2000 rows and dont want to copy and paste all
individually.

Have tried sorting the data, but due to other characters in that particular
cell cant get all SC rows together.
TIA
Ajay

Don't use the stars in the formula. it is looking for them

"Ajay" wrote:

Cheers bj
Tried this out and get all TRUE values. Probably doing something wrong.
Examples of values in target cells:
12562/SC
PRISMATICS/12531/SC
VALVES/12521/SC
FAI/SC
I entered =iserror(find("*SC*",E1)) is this incorrect?
It looks as if I will be doing this alot so any help with a macro would be
great
Thanks for your help
ajay

"bj" wrote:

You could do it in a macro
or
in a helper column
=iserror(find("SC",A1))
copy to the end of the data
use auto filter on this coulumn to find all the "False"
copy these rows and paste into the new worksheet

if you have to do it a lot, write the macro.
If you only have to do it once in a blue moon do it by hand.

"Ajay" wrote:

Afternoon All,
Had a look in discussions cant find what I am looking for.

Is it possible to use a macro to look for a certain portion of text in this
case "SC"
in a cell and copy the entire row to a new sheet.

Raw data is just over 2000 rows and dont want to copy and paste all
individually.

Have tried sorting the data, but due to other characters in that particular
cell cant get all SC rows together.
TIA
Ajay

Knew it would be simple thanks for your help bj
Ajay

"bj" wrote:

Don't use the stars in the formula. it is looking for them

"Ajay" wrote:

Cheers bj
Tried this out and get all TRUE values. Probably doing something wrong.
Examples of values in target cells:
12562/SC
PRISMATICS/12531/SC
VALVES/12521/SC
FAI/SC
I entered =iserror(find("*SC*",E1)) is this incorrect?
It looks as if I will be doing this alot so any help with a macro would be
great
Thanks for your help
ajay

"bj" wrote:

You could do it in a macro
or
in a helper column
=iserror(find("SC",A1))
copy to the end of the data
use auto filter on this coulumn to find all the "False"
copy these rows and paste into the new worksheet

if you have to do it a lot, write the macro.
If you only have to do it once in a blue moon do it by hand.

"Ajay" wrote:

Afternoon All,
Had a look in discussions cant find what I am looking for.

Is it possible to use a macro to look for a certain portion of text in this
case "SC"
in a cell and copy the entire row to a new sheet.

Raw data is just over 2000 rows and dont want to copy and paste all
individually.

Have tried sorting the data, but due to other characters in that particular
cell cant get all SC rows together.
TIA
Ajay

Another option...

Data|Filter|Autofilter
Filter on that column
Custom
contains SC

Copy the visible cells to the new location.

Ajay wrote:

Afternoon All,
Had a look in discussions cant find what I am looking for.

Is it possible to use a macro to look for a certain portion of text in this
case "SC"
in a cell and copy the entire row to a new sheet.

Raw data is just over 2000 rows and dont want to copy and paste all
individually.

Have tried sorting the data, but due to other characters in that particular
cell cant get all SC rows together.
TIA
Ajay

--

Dave Peterson

Thanks Dave one to remember for the future
Easy when you know how!
Thanks again
ajay



"Dave Peterson" wrote:

Another option...

Data|Filter|Autofilter
Filter on that column
Custom
contains SC

Copy the visible cells to the new location.

Ajay wrote:

Afternoon All,
Had a look in discussions cant find what I am looking for.

Is it possible to use a macro to look for a certain portion of text in this
case "SC"
in a cell and copy the entire row to a new sheet.

Raw data is just over 2000 rows and dont want to copy and paste all
individually.

Have tried sorting the data, but due to other characters in that particular
cell cant get all SC rows together.
TIA
Ajay

--

Dave Peterson