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.

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.

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.

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.

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.

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.