Conditional Copy

[jh]

I have a spreadsheet with 25,000 rows of data. I need to copy all
rows which have the same value in colum A to a new sheet. Is there
any way to do this automaticaally. Assume there are 50-60 different
sheets that need to be created. As a second condition could I have
this rename the name of the sheet to match the value in Column A?
Thanks

 

[Mark]

Hi,
you will need to ascertain what the common value is to
identify. For example: if any cell in column A has "YES"
then copy that entire row(?) and paste it ot a new sheet.
If you cannot get any help here, could you send some
sample data and the value to find, to me, and I will
create something.
See my stuff at:
http://www.geocities.com/excelmarksway

(e-mail address removed)

- -Mark

 

[Ron de Bruin]

Hi JH

Two ways
http://www.rondebruin.nl/copy5.htm

Or a add-in
http://www.rondebruin.nl/easyfilter.htm

 

[jh]

Ron,
It copied the vlaues in column A and renamed the sheets to match.
However the remaining values in the rwo are missing. Any thoughts?
Thanks for your help so far. BTW I've been looking for a way to sort
workshhets, found that through your help as well. Thanks/

 

[Ron de Bruin]

However the remaining values in the rwo are missing

Change your range then

With WS.Range("YourRange")

To something like this

With WS.Range("A1:Z1000")

 

[joshhyman]

Thaqt worked, thanks!

 

[joshhyman]

Ron,
I moved the code into a larger spreadsheet I have on this line
Set rng =
ws1.Range("=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))")
I get an error message: runtime error '9" subscript out of range.
If you'll notice I used the dynamic code for figuring out the range.
Any thoughts?
Thanks again.
JH

 

[Ron de Bruin]

Hi JH

Use the range name in the code.
See Debra's site for a example
http://www.contextures.com/xlNames01.html#Dynamic

 

[joshhyman]

I tried that, that is where the line I referenced above came from. I
substitued the last couint if to make the number of rows dynamic. I
guess I could just give it a value instead. Thanks

 

[Ron de Bruin]

Hi

After you used Debra's example use this in the code

Set rng = ws1.Range("NameList")