| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Conditional Copy
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 |
|
#2
|
|||
|
|||
|
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 - -Mark -----Original Message----- 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 . |
|
#3
|
|||
|
|||
|
Hi JH
Two ways http://www.rondebruin.nl/copy5.htm Or a add-in http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jh" wrote in message om... 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 |
|
#4
|
|||
|
|||
|
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" wrote in message ... Hi JH Two ways http://www.rondebruin.nl/copy5.htm Or a add-in http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jh" wrote in message om... 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 |
|
#5
|
|||
|
|||
|
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") -- Regards Ron de Bruin http://www.rondebruin.nl "jh" wrote in message om... 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" wrote in message ... Hi JH Two ways http://www.rondebruin.nl/copy5.htm Or a add-in http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "jh" wrote in message om... 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 |
|
#6
|
|||
|
|||
|
Thaqt worked, thanks!
|
|
#7
|
|||
|
|||
|
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 |
|
#8
|
|||
|
|||
|
Hi JH
Use the range name in the code. See Debra's site for a example http://www.contextures.com/xlNames01.html#Dynamic -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... 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 |
|
#9
|
|||
|
|||
|
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 |
|
#10
|
|||
|
|||
|
Hi
After you used Debra's example use this in the code Set rng = ws1.Range("NameList") -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatic backup copy | Charts and Charting in Excel | |||
| conditional format of data tables in charts | Charts and Charting in Excel | |||
| copy paste cell character limit | Excel Discussion (Misc queries) | |||
| Conditional display of a .jpeg file? | Excel Discussion (Misc queries) | |||
| Adding more than three Conditions to 'Conditional Formatting' | Excel Discussion (Misc queries) | |||
Linear Mode
