I am a "semi" newby and need help with a project.

I have a 26,000 item Excel file consisting of < 10 columns of info
(varies). I need to be able to look in a named column (variable) for a
keyword and, if found, copy that entire row to another workbook.

What I need the macro to do is ask:

"Search Which Column?"
"For What Keyword?"
"Run Now?"

and, when it finds that keyword in a column, copy the entire row to
another worksheet or workbook without destroying the info in the master
file.

I do not absolutly need to append info to the new worksheet but it
would be nice to be able to do so and I would use that function if I
had it.

This is WAY beyond my capabilities although I do know how to insert
macros.

I would think that this would be a helpful macro for many other people.
It probably has been written but I can't find it. Any help given will
be greatly appreciated.

Thanks - Jim K

Sub findinvarcol()
Dim mc As Long
On Error GoTo nofind
mc = InputBox("enter column")
kw = InputBox("Keyword?")
x = Columns(mc).Find(kw).Address
MsgBox x
Exit Sub
nofind: MsgBox "No find"
End Sub

--
Don Guillett
SalesAid Software

"Cessna Driver" wrote in message
ups.com...
I am a "semi" newby and need help with a project.

I have a 26,000 item Excel file consisting of < 10 columns of info
(varies). I need to be able to look in a named column (variable) for a
keyword and, if found, copy that entire row to another workbook.

What I need the macro to do is ask:

"Search Which Column?"
"For What Keyword?"
"Run Now?"

and, when it finds that keyword in a column, copy the entire row to
another worksheet or workbook without destroying the info in the master
file.

I do not absolutly need to append info to the new worksheet but it
would be nice to be able to do so and I would use that function if I
had it.

This is WAY beyond my capabilities although I do know how to insert
macros.

I would think that this would be a helpful macro for many other people.
It probably has been written but I can't find it. Any help given will
be greatly appreciated.

Thanks - Jim K

This should help you get started.

Sub Copy_By_Criteria()
Dim i As Integer
Dim iLastRow As Integer
Dim Collet As String
Dim Msg, Style
Set wks = ActiveSheet
Application.ScreenUpdating = False
Collet = InputBox("Choose Column Letter")
whatwant = InputBox("Choose Criteria" & Chr(13) _
& "Wildcards such as *PY* can be used")
Msg = "Run Now?"
Style = vbYesNo
Response = MsgBox(Msg, Style)
If Response = vbYes Then
iLastRow = wks.Cells(Rows.Count, Collet).End(xlUp).Row
For i = iLastRow To 1 Step -1
If wks.Cells(i, Collet).Value Like whatwant Then
wks.Rows(i).Copy Destination:= _
Sheets("Sheet7").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End If
Next i
End If
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On 2 Nov 2006 08:00:37 -0800, "Cessna Driver" wrote:

I am a "semi" newby and need help with a project.

I have a 26,000 item Excel file consisting of < 10 columns of info
(varies). I need to be able to look in a named column (variable) for a
keyword and, if found, copy that entire row to another workbook.

What I need the macro to do is ask:

"Search Which Column?"
"For What Keyword?"
"Run Now?"

and, when it finds that keyword in a column, copy the entire row to
another worksheet or workbook without destroying the info in the master
file.

I do not absolutly need to append info to the new worksheet but it
would be nice to be able to do so and I would use that function if I
had it.

This is WAY beyond my capabilities although I do know how to insert
macros.

I would think that this would be a helpful macro for many other people.
It probably has been written but I can't find it. Any help given will
be greatly appreciated.

Thanks - Jim K

It's almost working! It apparently findx the variable i input, copies
it but then runs into problems putting the info on another sheet or
other workbook. May I impose on you for a bit of trouble shooting. I
would do it myself but this whole thing baffles the heck out of me!

Regards,
Jim


Gord Dibben wrote:
This should help you get started.

Sub Copy_By_Criteria()
Dim i As Integer
Dim iLastRow As Integer
Dim Collet As String
Dim Msg, Style
Set wks = ActiveSheet
Application.ScreenUpdating = False
Collet = InputBox("Choose Column Letter")
whatwant = InputBox("Choose Criteria" & Chr(13) _
& "Wildcards such as *PY* can be used")
Msg = "Run Now?"
Style = vbYesNo
Response = MsgBox(Msg, Style)
If Response = vbYes Then
iLastRow = wks.Cells(Rows.Count, Collet).End(xlUp).Row
For i = iLastRow To 1 Step -1
If wks.Cells(i, Collet).Value Like whatwant Then
wks.Rows(i).Copy Destination:= _
Sheets("Sheet7").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End If
Next i
End If
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On 2 Nov 2006 08:00:37 -0800, "Cessna Driver" wrote:

I am a "semi" newby and need help with a project.

I have a 26,000 item Excel file consisting of < 10 columns of info
(varies). I need to be able to look in a named column (variable) for a
keyword and, if found, copy that entire row to another workbook.

What I need the macro to do is ask:

"Search Which Column?"
"For What Keyword?"
"Run Now?"

and, when it finds that keyword in a column, copy the entire row to
another worksheet or workbook without destroying the info in the master
file.

I do not absolutly need to append info to the new worksheet but it
would be nice to be able to do so and I would use that function if I
had it.

This is WAY beyond my capabilities although I do know how to insert
macros.

I would think that this would be a helpful macro for many other people.
It probably has been written but I can't find it. Any help given will
be greatly appreciated.

Thanks - Jim K

Whoops - My Fault! I only had one sheet. I added some more and found
the info i needed on sheet 7. So far so good! Thanks. Back to
testing!!!

Cessna Driver wrote:
It's almost working! It apparently findx the variable i input, copies
it but then runs into problems putting the info on another sheet or
other workbook. May I impose on you for a bit of trouble shooting. I
would do it myself but this whole thing baffles the heck out of me!

Regards,
Jim


Gord Dibben wrote:
This should help you get started.

Sub Copy_By_Criteria()
Dim i As Integer
Dim iLastRow As Integer
Dim Collet As String
Dim Msg, Style
Set wks = ActiveSheet
Application.ScreenUpdating = False
Collet = InputBox("Choose Column Letter")
whatwant = InputBox("Choose Criteria" & Chr(13) _
& "Wildcards such as *PY* can be used")
Msg = "Run Now?"
Style = vbYesNo
Response = MsgBox(Msg, Style)
If Response = vbYes Then
iLastRow = wks.Cells(Rows.Count, Collet).End(xlUp).Row
For i = iLastRow To 1 Step -1
If wks.Cells(i, Collet).Value Like whatwant Then
wks.Rows(i).Copy Destination:= _
Sheets("Sheet7").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End If
Next i
End If
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On 2 Nov 2006 08:00:37 -0800, "Cessna Driver" wrote:

I am a "semi" newby and need help with a project.

I have a 26,000 item Excel file consisting of < 10 columns of info
(varies). I need to be able to look in a named column (variable) for a
keyword and, if found, copy that entire row to another workbook.

What I need the macro to do is ask:

"Search Which Column?"
"For What Keyword?"
"Run Now?"

and, when it finds that keyword in a column, copy the entire row to
another worksheet or workbook without destroying the info in the master
file.

I do not absolutly need to append info to the new worksheet but it
would be nice to be able to do so and I would use that function if I
had it.

This is WAY beyond my capabilities although I do know how to insert
macros.

I would think that this would be a helpful macro for many other people.
It probably has been written but I can't find it. Any help given will
be greatly appreciated.

Thanks - Jim K

Whoops - My Fault! I only had one sheet. I added some more and found
the info i needed on sheet 7. So far so good! Thanks. Back to
testing!!!

Cessna Driver wrote:
It's almost working! It apparently findx the variable i input, copies
it but then runs into problems putting the info on another sheet or
other workbook. May I impose on you for a bit of trouble shooting. I
would do it myself but this whole thing baffles the heck out of me!

Regards,
Jim


Gord Dibben wrote:
This should help you get started.

Sub Copy_By_Criteria()
Dim i As Integer
Dim iLastRow As Integer
Dim Collet As String
Dim Msg, Style
Set wks = ActiveSheet
Application.ScreenUpdating = False
Collet = InputBox("Choose Column Letter")
whatwant = InputBox("Choose Criteria" & Chr(13) _
& "Wildcards such as *PY* can be used")
Msg = "Run Now?"
Style = vbYesNo
Response = MsgBox(Msg, Style)
If Response = vbYes Then
iLastRow = wks.Cells(Rows.Count, Collet).End(xlUp).Row
For i = iLastRow To 1 Step -1
If wks.Cells(i, Collet).Value Like whatwant Then
wks.Rows(i).Copy Destination:= _
Sheets("Sheet7").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End If
Next i
End If
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On 2 Nov 2006 08:00:37 -0800, "Cessna Driver" wrote:

I am a "semi" newby and need help with a project.

I have a 26,000 item Excel file consisting of < 10 columns of info
(varies). I need to be able to look in a named column (variable) for a
keyword and, if found, copy that entire row to another workbook.

What I need the macro to do is ask:

"Search Which Column?"
"For What Keyword?"
"Run Now?"

and, when it finds that keyword in a column, copy the entire row to
another worksheet or workbook without destroying the info in the master
file.

I do not absolutly need to append info to the new worksheet but it
would be nice to be able to do so and I would use that function if I
had it.

This is WAY beyond my capabilities although I do know how to insert
macros.

I would think that this would be a helpful macro for many other people.
It probably has been written but I can't find it. Any help given will
be greatly appreciated.

Thanks - Jim K