I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

....but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
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


"Rumish8086" wrote in message ...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

That is extremely helpful. You have saved my life, sir. Thank you.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
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


"Rumish8086" wrote in message ...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

My only problem is (please bare with my here):

The column that needs to be searched for the user-entered string is Column
F. I don't entirely understand your code upon first reading (this is
literally the first time I have ever even seen VB), but I do know that when
the default "A1" is entered, it does not seem to work. (i.e. I know that a
few of the cells in the F column have the word "Pittsburgh" in them, but when
I search for that string, it gives me a blank screen.)

Yet when I replace "A1" with "F1," this too does not work.

What's happening?

Thanks.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
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


"Rumish8086" wrote in message ...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

You are welcome

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
That is extremely helpful. You have saved my life, sir. Thank you.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
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


"Rumish8086" wrote in message ...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message
...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

Oh, I see part of the problem already. Your implentation of AutoFilter is set
to Exact Matches rather than "Contains." How do I switch it to Contains
rather than Exact Match (i.e. a User can search for "Pittsburgh" and get the
the term "Only in Pittsburgh" as a result?)

I think that will solve the problem.

"Rumish8086" wrote:

My only problem is (please bare with my here):

The column that needs to be searched for the user-entered string is Column
F. I don't entirely understand your code upon first reading (this is
literally the first time I have ever even seen VB), but I do know that when
the default "A1" is entered, it does not seem to work. (i.e. I know that a
few of the cells in the F column have the word "Pittsburgh" in them, but when
I search for that string, it gives me a blank screen.)

Yet when I replace "A1" with "F1," this too does not work.

What's happening?

Thanks.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
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


"Rumish8086" wrote in message ...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

This code assume that your data start in A1 and that this is the header cell of the first column
I filter column 1 in this example (A)

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

If your data start in A and you want to filter in F

Use this then (column 6 = F)
rng.AutoFilter Field:=6, Criteria1:=Str

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
My only problem is (please bare with my here):

The column that needs to be searched for the user-entered string is Column
F. I don't entirely understand your code upon first reading (this is
literally the first time I have ever even seen VB), but I do know that when
the default "A1" is entered, it does not seem to work. (i.e. I know that a
few of the cells in the F column have the word "Pittsburgh" in them, but when
I search for that string, it gives me a blank screen.)

Yet when I replace "A1" with "F1," this too does not work.

What's happening?

Thanks.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
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


"Rumish8086" wrote in message ...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message
...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

Ah-HA! So what is the problem is not the A1s -- in fact, that's perfectly
right...the part that determines which column is AutoFiltered is this:

rng.AutoFilter Field:=6, Criteria1:=Str

So then my LAST QUESTION remains: How do I get the AutoFilter to filter out
not just EXACT MATCHES of the string that the user has typed in, but also
matches that just CONTAIN the string?

Thanks!!

"Rumish8086" wrote:

Oh, I see part of the problem already. Your implentation of AutoFilter is set
to Exact Matches rather than "Contains." How do I switch it to Contains
rather than Exact Match (i.e. a User can search for "Pittsburgh" and get the
the term "Only in Pittsburgh" as a result?)

I think that will solve the problem.

"Rumish8086" wrote:

My only problem is (please bare with my here):

The column that needs to be searched for the user-entered string is Column
F. I don't entirely understand your code upon first reading (this is
literally the first time I have ever even seen VB), but I do know that when
the default "A1" is entered, it does not seem to work. (i.e. I know that a
few of the cells in the F column have the word "Pittsburgh" in them, but when
I search for that string, it gives me a blank screen.)

Yet when I replace "A1" with "F1," this too does not work.

What's happening?

Thanks.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
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


"Rumish8086" wrote in message ...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

I figured this out just before you posted it!

My last question -- and thank you very much, you have been an absolute
life-saver -- is how I can search for strings CONTAINING the user-defined
search term, not solely strings that are EXACT MATCHES for it.

I recorded a macro where I searched for a string CONTAINING the word
"pittsburgh" and took a look at the code...the difference seems to be that,
rather than searching purely for the string "Pittsburgh" alone, the code now
searches for the string "=*Pittsburgh*" -- or the string "Pittsburgh" between
two wildcards. Makes sense.

I just don't know how to do that with a user-entered variable rather than a
string. This is the last thing I will need for today...Phew!

"Ron de Bruin" wrote:

This code assume that your data start in A1 and that this is the header cell of the first column
I filter column 1 in this example (A)

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

If your data start in A and you want to filter in F

Use this then (column 6 = F)
rng.AutoFilter Field:=6, Criteria1:=Str

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
My only problem is (please bare with my here):

The column that needs to be searched for the user-entered string is Column
F. I don't entirely understand your code upon first reading (this is
literally the first time I have ever even seen VB), but I do know that when
the default "A1" is entered, it does not seem to work. (i.e. I know that a
few of the cells in the F column have the word "Pittsburgh" in them, but when
I search for that string, it gives me a blank screen.)

Yet when I replace "A1" with "F1," this too does not work.

What's happening?

Thanks.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
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


"Rumish8086" wrote in message ...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message
...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

Use this then

rng.AutoFilter Field:=6, Criteria1:="*" & Str & "*"


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
Ah-HA! So what is the problem is not the A1s -- in fact, that's perfectly
right...the part that determines which column is AutoFiltered is this:

rng.AutoFilter Field:=6, Criteria1:=Str

So then my LAST QUESTION remains: How do I get the AutoFilter to filter out
not just EXACT MATCHES of the string that the user has typed in, but also
matches that just CONTAIN the string?

Thanks!!

"Rumish8086" wrote:

Oh, I see part of the problem already. Your implentation of AutoFilter is set
to Exact Matches rather than "Contains." How do I switch it to Contains
rather than Exact Match (i.e. a User can search for "Pittsburgh" and get the
the term "Only in Pittsburgh" as a result?)

I think that will solve the problem.

"Rumish8086" wrote:

My only problem is (please bare with my here):

The column that needs to be searched for the user-entered string is Column
F. I don't entirely understand your code upon first reading (this is
literally the first time I have ever even seen VB), but I do know that when
the default "A1" is entered, it does not seem to work. (i.e. I know that a
few of the cells in the F column have the word "Pittsburgh" in them, but when
I search for that string, it gives me a blank screen.)

Yet when I replace "A1" with "F1," this too does not work.

What's happening?

Thanks.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
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


"Rumish8086" wrote in message
...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message
...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!

This will work

rng.AutoFilter Field:=6, Criteria1:="*" & Str & "*"


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
I figured this out just before you posted it!

My last question -- and thank you very much, you have been an absolute
life-saver -- is how I can search for strings CONTAINING the user-defined
search term, not solely strings that are EXACT MATCHES for it.

I recorded a macro where I searched for a string CONTAINING the word
"pittsburgh" and took a look at the code...the difference seems to be that,
rather than searching purely for the string "Pittsburgh" alone, the code now
searches for the string "=*Pittsburgh*" -- or the string "Pittsburgh" between
two wildcards. Makes sense.

I just don't know how to do that with a user-entered variable rather than a
string. This is the last thing I will need for today...Phew!

"Ron de Bruin" wrote:

This code assume that your data start in A1 and that this is the header cell of the first column
I filter column 1 in this example (A)

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

If your data start in A and you want to filter in F

Use this then (column 6 = F)
rng.AutoFilter Field:=6, Criteria1:=Str

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message ...
My only problem is (please bare with my here):

The column that needs to be searched for the user-entered string is Column
F. I don't entirely understand your code upon first reading (this is
literally the first time I have ever even seen VB), but I do know that when
the default "A1" is entered, it does not seem to work. (i.e. I know that a
few of the cells in the F column have the word "Pittsburgh" in them, but when
I search for that string, it gives me a blank screen.)

Yet when I replace "A1" with "F1," this too does not work.

What's happening?

Thanks.

"Ron de Bruin" wrote:

Install EasyFilter if you want it to be easy

But with code you can do this
http://www.rondebruin.nl/copy5.htm#one

I add a inputbox in the code example below

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim Str As String

Str = InputBox("Enter the string")
If Trim(Str) = "" Then Exit Sub

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=Str

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
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


"Rumish8086" wrote in message
...
I understand how AutoFilter works, and what it does is ultimately what I
would like to do...

...but I would like to make it so that, rather than the User having to go
through the process of AutoFiltering him/herself, all that he/she would have
to do is: type the search string into a BOX, hit ENTER, and have a NEW
WORKSHEET that contains only rows of text with the search string he/she
searched for.

Do you see what I mean?

Thanks.

"Ron de Bruin" wrote:

Hi

If the value is in one column ? you can use DataAutoFilter to do this

See
http://www.contextures.com/xlautofilter01.html

I have a add-in that you can use
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rumish8086" wrote in message
...
I apologize for posting this a second time (it is already on the Programming
board), but I need help on this as quickly as possible and thought that
perhaps some people who read this one could help me as well.

Thank you again.

===

My task is to make a form which does the following: It needs to search an
Excel worksheet for a string of text entered by the user and then spit out a
new worksheet with only rows containing that data.

Seems like it should be very easy, but I am completely new to Excel and all
of my attempts thus far have failed. I'm trying to learn some basic VBA as
quick as I can, but some help would be absolutely wonderful.

Basically, the way it should look is this: there should be a box which asks
for text, let's call this SearchString. And then there should be a button
beneath it that begins the search process (let's call this SearchStart).
After pressing the SearchStart button, the user should see all rows
containing the previously-entered SearchString in a new worksheet.

Easy as that, but I don't know where to begin. Can someone help?

Thanks!