Find Value in cell via Inputbox, copy row, paste in new worksheet

L

lthursdayl

New Member
Joined
Aug 5, 2009
Messages
3
Hello Everyone,
I have been trying to write a macro in VB that will search for a word in a cell, the word would be input by the user via a inputbox, search the entire worksheet, then copy the entire row the word is found on, and paste the row into a new worksheet that is part of the same workbook. I am not attaching the code I have been working on because it is awful and I think I have been going about this in the wrong way. Any help is greatly appreciated.
Thanks in advance.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
A few things...

Any clue as to what column this word might be in or is it anywhere in the used range.

If the word is "hello" do you want to copy rows whose cell(s) contain
Hello (note the upper case)
I said hello (note the "hello" as part of the cell)
hello (note the "hello" is the only item in the cell)

So clarfy that and
- what row your data starts on as this might be a filter issue
- the name of the sheet you want to copy the row(s) to
- if you want only the first found instance or all rows where this word is found.
 
Upvote 0
to answer your questions.

-if the word was "hello" it could be anywhere in the range
-I would like it to copy "hello" if "Hello" was the entered value
-and hello could be part or the whole of the cell
- Last I would like all the instances of the word found, not just the first
I did try using a filter, but becuase the word ex. "Hello" could be in any column it did not work.
-the lets just say the name of the sheet I want to copy to would be "sheet2"

Thanks
 
Upvote 0
This macro will copy whatever row in the used range holds a cell where "hello" is part of the text.

The destination (where these rows are copied to) is Sheet2 starting in row 2.

Stick this macro into a standard module, then go to the worksheet that has these "hello" words, and run the macro, worked fine for me, no problems.

Code: 
Sub Test1()
Application.ScreenUpdating = True
Dim xRow&, NextRow&, LastRow&
NextRow = 2
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For xRow = 1 To LastRow
If WorksheetFunction.CountIf(Rows(xRow), "*hello*") > 0 Then
Rows(xRow).Copy Sheets("Sheet2").Rows(NextRow)
NextRow = NextRow + 1
End If
Next xRow
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Still too early in California, this macro will copy whatever row in the used range holds a cell where the word entered into the InputBox exists, plus I set ScreenUpdating to True at the beginning of my last macro when it should have been set to False. Brewing my coffee now.

The destination (where these rows are copied to) is Sheet2 starting in row 2.

Stick this macro into a standard module, then go to the worksheet that holds your table of rows to maybe be copied and run the macro, tested OK.

Code: 
Sub Test2()
Dim myWord$
myWord = InputBox("What key word to copy rows", "Enter your word")
If myWord = "" Then Exit Sub
 
Application.ScreenUpdating = False
Dim xRow&, NextRow&, LastRow&
NextRow = 2
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For xRow = 1 To LastRow
If WorksheetFunction.CountIf(Rows(xRow), "*" & myWord & "*") > 0 Then
Rows(xRow).Copy Sheets("Sheet2").Rows(NextRow)
NextRow = NextRow + 1
End If
Next xRow
Application.ScreenUpdating = True
 
MsgBox "Macro is complete, " & NextRow - 2 & " rows containing" & vbCrLf & _
"''" & myWord & "''" & " were copied to Sheet2.", 64, "Done"
End Sub
 
Last edited:
Upvote 0
Thank you so much for your help.
Macro works great, had been fighting with the code for about a week before this. Saved me time/headaches.
Thanks again.
 
Upvote 0
Tom! This is great.., I would like to request for an additional help here, can this copy paste also have an offset feature?
For example: using this macro, the entire row is copied from from sheet2 to sheet1 when using a keyword..which is great!
however, if am using the same macro multiple times and everytime the data overlaps the existing one.
Is there any possibility to use offset funtion to copy paste the data in the next blank row, when the macro is used multiple times??
Any suggestions would be great.. Thanks in Advance.
 
Upvote 0
Not *exactly* sure I am following you, but for starters, you wrote this...
"the entire row is copied from from sheet2 to sheet1 when using a keyword"
...yet really, the code I wrote copies from some sheet and pastes to sheet2.

Assuming that was what you meant, and that you just want to keep stacking copied records into Sheet2 starting at the next available row (again, Sheet2 is the destination "copy to" sheet), then all you'd need to do is substitute this statement...
NextRow = 2

to this...
NextRow = sheets("Sheet2").Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
 
Upvote 0
Hi Tom, First of all thank you so much for taking time to reply for an old post..really appreciate that. I wanted to give a specific explanation on this..
I'll give you an example in this. I have a command button to search a keyword in sheet2 and copy the related rows to sheet1.
If I click this button for the first time, say for example the data is copied to A1 to H20 rows in Sheet1, if I click the button the second time, the data should be copied to A21 to H43 (for example)...
How do I set an offset function to it, so that I dont overlap the data that is already copied. is there any way that I can define the exact cells that it needs to be pasted? I have no idea how to work it out.
Would be great if you could help having a look on this, your suggestions are really appreciated. Thanks.
 
Upvote 0
OK, so here are some points for clarification:

• When you say "command button" do you mean a button from the activex control toolbox that was drawn onto the sheet, maybe the button is named CommandButton1, as opposed to a button drawn from the Forms toolbar.

• Is there any column in particular on Sheet 2 where this keyword would only need to be searched for, or can it exist anywhere among columns A:H.

• Is this keyword alone in the cell, or part of another longer string. Example, if the keyword is "stop" should only a cell with "stop" have its row copied, or if the cell holds "I stop for pedestrians" or "The rain was unstoppable" should that also be copied.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Copy /past cells by inputbox on sheet 2 to sheet1 using VBA
2
Replies
12
Views
670
DanteAmor
DanteAmor
U
  • Solved
Copying ranges while eliminating blanks yields unexpected results
Replies
3
Views
66
Alex Blakenburg
A
B
  • Question Question
Stupid Question About Copying Rows
Replies
5
Views
137
MARK858
MARK858
C
  • Question Question
FIND does not pick up phrases unless its an exact match. As explained below
Replies
1
Views
93
JamesCanale
JamesCanale
N
  • Question Question
Finding the differences then copy and paste code to a new sheet
Replies
1
Views
697
myall_blues
myall_blues

Forum statistics

Threads
1,225,157
Messages
6,183,250
Members
453,152
Latest member
ChrisMd

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top