Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
extract info to another sheet..
Hi...
I have a sheet with 10,000 rows of various data. The task is to create code that will generate an input box. The value entered within the box will execute further code that will cut and paste the rows on which cells are located, where the input value is matched. eg. I want all rows that contain any cell with for example Jones, deleted from the active sheet and pasted onto sheet2. I also want to know how many rows were deleted. I did have a nice snippet of code for this but it suddenly created a run-time error 13 that has proved a nightmare tro resolve so its back to square 1. Any help at xmas for a lowly novice would be really useful. Happy xmas. Gordon. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
extract info to another sheet..
Hi Gordon
Maybe this will help http://www.rondebruin.nl/copy5.htm Or an Add-in http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Gordon" wrote in message ... Hi... I have a sheet with 10,000 rows of various data. The task is to create code that will generate an input box. The value entered within the box will execute further code that will cut and paste the rows on which cells are located, where the input value is matched. eg. I want all rows that contain any cell with for example Jones, deleted from the active sheet and pasted onto sheet2. I also want to know how many rows were deleted. I did have a nice snippet of code for this but it suddenly created a run-time error 13 that has proved a nightmare tro resolve so its back to square 1. Any help at xmas for a lowly novice would be really useful. Happy xmas. Gordon. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
extract info to another sheet..
Ron...thanks...
Am I right is saying your examples look only in the first column for the value? How can I adapt this script to look at every cell, then to automatically paste any row in which the value is found into a new sheet. THanks in advance... Gordon. "Ron de Bruin" wrote: Hi Gordon Maybe this will help http://www.rondebruin.nl/copy5.htm Or an Add-in http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Gordon" wrote in message ... Hi... I have a sheet with 10,000 rows of various data. The task is to create code that will generate an input box. The value entered within the box will execute further code that will cut and paste the rows on which cells are located, where the input value is matched. eg. I want all rows that contain any cell with for example Jones, deleted from the active sheet and pasted onto sheet2. I also want to know how many rows were deleted. I did have a nice snippet of code for this but it suddenly created a run-time error 13 that has proved a nightmare tro resolve so its back to square 1. Any help at xmas for a lowly novice would be really useful. Happy xmas. Gordon. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
extract info to another sheet..
Hi Gordon
Am I right is saying your examples look only in the first column Yes that is correct You can add a column to your sheet with a formula like this =IF(COUNTIF(A2:G2,"jones")=0,"","copy") And use the code example from my site on this column -- Regards Ron de Bruin http://www.rondebruin.nl "Gordon" wrote in message ... Ron...thanks... Am I right is saying your examples look only in the first column for the value? How can I adapt this script to look at every cell, then to automatically paste any row in which the value is found into a new sheet. THanks in advance... Gordon. "Ron de Bruin" wrote: Hi Gordon Maybe this will help http://www.rondebruin.nl/copy5.htm Or an Add-in http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Gordon" wrote in message ... Hi... I have a sheet with 10,000 rows of various data. The task is to create code that will generate an input box. The value entered within the box will execute further code that will cut and paste the rows on which cells are located, where the input value is matched. eg. I want all rows that contain any cell with for example Jones, deleted from the active sheet and pasted onto sheet2. I also want to know how many rows were deleted. I did have a nice snippet of code for this but it suddenly created a run-time error 13 that has proved a nightmare tro resolve so its back to square 1. Any help at xmas for a lowly novice would be really useful. Happy xmas. Gordon. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
extract info to another sheet..
Hi Ron...
My sheet is 45 colums wide and 10,000 rows deep. The data I seek can be in any cell. My intention is for my code to find this data and then cut and paste the row(s) on which the data was found onto a new sheet. The rows that have been cut will be deteled from the master list and no gaps will show. Does this make any sense...can you still help? "Ron de Bruin" wrote: Hi Gordon Am I right is saying your examples look only in the first column Yes that is correct You can add a column to your sheet with a formula like this =IF(COUNTIF(A2:G2,"jones")=0,"","copy") And use the code example from my site on this column -- Regards Ron de Bruin http://www.rondebruin.nl "Gordon" wrote in message ... Ron...thanks... Am I right is saying your examples look only in the first column for the value? How can I adapt this script to look at every cell, then to automatically paste any row in which the value is found into a new sheet. THanks in advance... Gordon. "Ron de Bruin" wrote: Hi Gordon Maybe this will help http://www.rondebruin.nl/copy5.htm Or an Add-in http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Gordon" wrote in message ... Hi... I have a sheet with 10,000 rows of various data. The task is to create code that will generate an input box. The value entered within the box will execute further code that will cut and paste the rows on which cells are located, where the input value is matched. eg. I want all rows that contain any cell with for example Jones, deleted from the active sheet and pasted onto sheet2. I also want to know how many rows were deleted. I did have a nice snippet of code for this but it suddenly created a run-time error 13 that has proved a nightmare tro resolve so its back to square 1. Any help at xmas for a lowly novice would be really useful. Happy xmas. Gordon. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
extract info to another sheet..
Hi Gordon
In cell AT2 (col = 46) this formula =IF(COUNTIF(A2:AS2,"jones")=0,"","copy") And copy down till AT10000 And run this macro Change the sheet name <<Set WS = Sheets("yoursheet") Sub Copy_With_AutoFilter() Dim WS As Worksheet Dim WSNew As Worksheet Dim Str As String Set WS = Sheets("yoursheet") Str = "Copy" With WS.Range("A1:AT10000") .AutoFilter Field:=46, Criteria1:=Str Set WSNew = Sheets.Add .Cells.SpecialCells(xlCellTypeVisible).Copy WSNew.Range("A1") .Cells.EntireRow.Delete End With WS.AutoFilterMode = False On Error Resume Next WSNew.Name = Str If Err.Number 0 Then MsgBox "Change the name of : " & WSNew.Name & " manually" Err.Clear End If On Error GoTo 0 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Gordon" wrote in message ... Hi Ron... My sheet is 45 colums wide and 10,000 rows deep. The data I seek can be in any cell. My intention is for my code to find this data and then cut and paste the row(s) on which the data was found onto a new sheet. The rows that have been cut will be deteled from the master list and no gaps will show. Does this make any sense...can you still help? "Ron de Bruin" wrote: Hi Gordon Am I right is saying your examples look only in the first column Yes that is correct You can add a column to your sheet with a formula like this =IF(COUNTIF(A2:G2,"jones")=0,"","copy") And use the code example from my site on this column -- Regards Ron de Bruin http://www.rondebruin.nl "Gordon" wrote in message ... Ron...thanks... Am I right is saying your examples look only in the first column for the value? How can I adapt this script to look at every cell, then to automatically paste any row in which the value is found into a new sheet. THanks in advance... Gordon. "Ron de Bruin" wrote: Hi Gordon Maybe this will help http://www.rondebruin.nl/copy5.htm Or an Add-in http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Gordon" wrote in message ... Hi... I have a sheet with 10,000 rows of various data. The task is to create code that will generate an input box. The value entered within the box will execute further code that will cut and paste the rows on which cells are located, where the input value is matched. eg. I want all rows that contain any cell with for example Jones, deleted from the active sheet and pasted onto sheet2. I also want to know how many rows were deleted. I did have a nice snippet of code for this but it suddenly created a run-time error 13 that has proved a nightmare tro resolve so its back to square 1. Any help at xmas for a lowly novice would be really useful. Happy xmas. Gordon. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract info from one sheet to another | Excel Worksheet Functions | |||
Would it be possible to extract info from this site? | Excel Discussion (Misc queries) | |||
Please help: Extract some info. from a cell | Excel Worksheet Functions | |||
Compare 2 Sheets and Extract Unique Info to a 3rd Sheet | Excel Discussion (Misc queries) | |||
Using formula to extract info | Excel Programming |