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

[lthursdayl]

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.

 

[Jimmypop]

Good day

I am also using this code

Private Sub CommandButton2_Click()
Dim myWord$
myWord = InputBox("Input Machine Type without spaces", "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("Consolidation").Rows(NextRow)
NextRow = NextRow + 1
End If
Next xRow
Application.ScreenUpdating = True
 
MsgBox "Search is complete, " & NextRow - 2 & " Machines with search criteria" & vbCrLf & _
"''" & myWord & "''" & " were copied to Consolidation.", 64, "Done"
Sheets("Consolidation").Visible = True
ThisWorkbook.Sheets("Consolidation").Activate
End Sub

Now my problem is that it only copies column B, where search criteria is located, and column D and pastes that into Column A and B onto sheet "Consolidation" instead of copying entire row, e.g. Row 4 columns A:J

 

[kapil_ajmera]

[FONT=Verdana]Sub Test1()[/FONT]Application.ScreenUpdating = TrueDim xRow&, NextRow&, LastRow&NextRow = 2LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).RowFor xRow = 1 To LastRowIf WorksheetFunction.CountIf(Rows(xRow), "*hello*") > 0 ThenRows(xRow).Copy Sheets("Sheet2").Rows(NextRow)NextRow = NextRow + 1End IfNext xRowApplication.ScreenUpdating = True [FONT=Verdana]End Sub[/FONT]</pre>

Dear Sir from your code I have create this work book . I have small problem . I want from Sheet cash Macro Run and open search box and what I type the word in box it search the row and copy A to E to sheet1 only value and format not formula. Please help below is my sheet and password is welcome
Thanks in Advance

https://www.sendspace.com/file/7omh4c

 

[Carguy37122]

Tom,

I have to start off by saying... YOU DA MAN!

Is there a way to modify the code to reference a worksheet instead of the input box?

Here is an example of what I am trying to search against prior sales history...

In this example, I'm searching the 1st 8 digits of the VIN#s listed to find previous sales transactions. "# of Matches" is based on my previous formula, I can see how many matches currently exist against the VIN#.

Also, were you able to provide a solution to Jason Joel's question?

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

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.​

[TABLE="width: 620"]
<tbody>[TR]
[TD]VIN# - 8 Digits[/TD]
[TD]Year[/TD]
[TD]Make[/TD]
[TD]Model[/TD]
[TD]Trim[/TD]
[TD]Miles[/TD]
[TD]# of Matches[/TD]
[/TR]
[TR]
[TD]1G6DC5EY[/TD]
[TD]2011[/TD]
[TD]CADILLAC[/TD]
[TD]CTS[/TD]
[TD][/TD]
[TD]35,958[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1G1PE5SB[/TD]
[TD]2014[/TD]
[TD]CHEVY[/TD]
[TD]CRUZE[/TD]
[TD][/TD]
[TD]23,176[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]1G1PG5SC[/TD]
[TD]2012[/TD]
[TD]CHEVY[/TD]
[TD]CRUZE[/TD]
[TD][/TD]
[TD]54,261[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]2G1WC5E3[/TD]
[TD]2014[/TD]
[TD]CHEVY[/TD]
[TD]IMPALA[/TD]
[TD][/TD]
[TD]19,713[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]2G1WB5E3[/TD]
[TD]2014[/TD]
[TD]CHEVY[/TD]
[TD]IMPALA[/TD]
[TD][/TD]
[TD]13,949[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]2G1WB5E3[/TD]
[TD]2014[/TD]
[TD]CHEVY[/TD]
[TD]IMPALA[/TD]
[TD]LT[/TD]
[TD]16,326[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]2G1125S3[/TD]
[TD]2014[/TD]
[TD]CHEVY[/TD]
[TD]IMPALA[/TD]
[TD]2LT[/TD]
[TD]14,952[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]2G1WG5E3[/TD]
[TD]2013[/TD]
[TD]CHEVY[/TD]
[TD]IMPALA[/TD]
[TD][/TD]
[TD]27,506[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]1G11H5SA[/TD]
[TD]2013[/TD]
[TD]CHEVY[/TD]
[TD]MALIBU[/TD]
[TD][/TD]
[TD]44,518[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1C3CCBBG[/TD]
[TD]2014[/TD]
[TD]CHRYSLER[/TD]
[TD]200[/TD]
[TD][/TD]
[TD]18,647[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]1C3CCBBG[/TD]
[TD]2014[/TD]
[TD]CHRYSLER[/TD]
[TD]200[/TD]
[TD]TOURING[/TD]
[TD]16,613[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]1C3CCBBG[/TD]
[TD]2014[/TD]
[TD]CHRYSLER[/TD]
[TD]200[/TD]
[TD]TOURING[/TD]
[TD]28,845[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]1C3CCBBG[/TD]
[TD]2014[/TD]
[TD]CHRYSLER[/TD]
[TD]200[/TD]
[TD]TOURING[/TD]
[TD]18,025[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]2C3CCAET[/TD]
[TD]2012[/TD]
[TD]CHRYSLER[/TD]
[TD]300C[/TD]
[TD][/TD]
[TD]38,741[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]2C3CDXDT[/TD]
[TD]2014[/TD]
[TD]DODGE[/TD]
[TD]CHARGER[/TD]
[TD]R/T[/TD]
[TD]19,063[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3C4PDCBG[/TD]
[TD]2014[/TD]
[TD]DODGE[/TD]
[TD]JOURNEY[/TD]
[TD][/TD]
[TD]20,295[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]3C4PDCBG[/TD]
[TD]2014[/TD]
[TD]DODGE[/TD]
[TD]JOURNEY[/TD]
[TD][/TD]
[TD]17,848[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]2FMDK3JC[/TD]
[TD]2013[/TD]
[TD]FORD[/TD]
[TD]EDGE[/TD]
[TD]SEL[/TD]
[TD]32,426[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]3GTP2VE3[/TD]
[TD]2011[/TD]
[TD]GMC[/TD]
[TD]SIERRA[/TD]
[TD][/TD]
[TD]46,409[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1C4NJDBB[/TD]
[TD]2014[/TD]
[TD]JEEP[/TD]
[TD]COMPASS[/TD]
[TD]4X[/TD]
[TD]17,835[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1C4NJDBB[/TD]
[TD]2014[/TD]
[TD]JEEP[/TD]
[TD]COMPASS[/TD]
[TD]4X[/TD]
[TD]24,456[/TD]
[TD]7[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[Tom Urtis]

Thanks for the compliment.

Just dealing with your question for now, because I get PMs and emails out of view from the forum's view, I'm not following your question:
"Is there a way to modify the code to reference a worksheet instead of the input box?"

If by chance you want to identify the name of the worksheet that your depicted table resides upon, you can substitute the InputBox code with a more meaningful variable, such as...

Dim mySheet as String
mySheet = Activesheet.Name

which assumes you will be executing (running) the macro when you know that this table-containing sheet will be the active sheet at that time of macro execution. Then, use the mySheet variable as needed in your macro.

If you mean something else, please provide more details.

 

[Carguy37122]

Tom,

Thanks for the quick reply!

In this workbook, I have 3 worksheets: "2014 Sales Matrix", "Seller List" and "Master Summary".

Seller list is where I want to paste VIN#s to research against the worksheet named 2014 Sales Matrix. When I used your input code, I can paste a VIN# in the box and pull all the data listed for each row that references the searched VIN# and paste it to worksheet named Master Summary.

When I replaced

Dim myWord$
myWord = InputBox("What key word to copy rows", "Type or Paste 1st 8 digits of VIN#")

with

Dim mySheet as String
mySheet = Activesheet.Name

It didn't do anything. Is there a way to specify the Activesheet as Seller List (as this is were I the referenced VIN#s are listed?

Thanks again!

 

[Tom Urtis]

I'm almost there but still lost. Two questions.

Question 1
What is the name of the sheet where you have this:

[TABLE="class: cms_table, width: 620"]
<tbody>[TR]
[TD]VIN# - 8 Digits[/TD]
[TD]Year[/TD]
[TD]Make[/TD]
[TD]Model[/TD]
[TD]Trim[/TD]
[TD]Miles[/TD]
[TD]# of Matches[/TD]
[/TR]
[TR]
[TD]1G6DC5EY[/TD]
[TD]2011[/TD]
[TD]CADILLAC[/TD]
[TD]CTS[/TD]
[TD][/TD]
[TD]35,958[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1G1PE5SB[/TD]
[TD]2014[/TD]
[TD]CHEVY[/TD]
[TD]CRUZE[/TD]
[TD][/TD]
[TD]23,176[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]1G1PG5SC[/TD]
[TD]2012[/TD]
[TD]CHEVY[/TD]
[TD]CRUZE[/TD]
[TD][/TD]
[TD]54,261[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]2G1WC5E3[/TD]
[TD]2014[/TD]
[TD]CHEVY[/TD]
[TD]IMPALA[/TD]
[TD][/TD]
[TD]19,713[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]2G1WB5E3[/TD]
[TD]2014[/TD]
[TD]CHEVY[/TD]
[TD]IMPALA[/TD]
[TD][/TD]
[TD]13,949[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]2G1WB5E3[/TD]
[TD]2014[/TD]
[TD]CHEVY[/TD]
[TD]IMPALA[/TD]
[TD]LT[/TD]
[TD]16,326[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]2G1125S3[/TD]
[TD]2014[/TD]
[TD]CHEVY[/TD]
[TD]IMPALA[/TD]
[TD]2LT[/TD]
[TD]14,952[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]2G1WG5E3[/TD]
[TD]2013[/TD]
[TD]CHEVY[/TD]
[TD]IMPALA[/TD]
[TD][/TD]
[TD]27,506[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]1G11H5SA[/TD]
[TD]2013[/TD]
[TD]CHEVY[/TD]
[TD]MALIBU[/TD]
[TD][/TD]
[TD]44,518[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1C3CCBBG[/TD]
[TD]2014[/TD]
[TD]CHRYSLER[/TD]
[TD]200[/TD]
[TD][/TD]
[TD]18,647[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]1C3CCBBG[/TD]
[TD]2014[/TD]
[TD]CHRYSLER[/TD]
[TD]200[/TD]
[TD]TOURING[/TD]
[TD]16,613[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]1C3CCBBG[/TD]
[TD]2014[/TD]
[TD]CHRYSLER[/TD]
[TD]200[/TD]
[TD]TOURING[/TD]
[TD]28,845[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]1C3CCBBG[/TD]
[TD]2014[/TD]
[TD]CHRYSLER[/TD]
[TD]200[/TD]
[TD]TOURING[/TD]
[TD]18,025[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]2C3CCAET[/TD]
[TD]2012[/TD]
[TD]CHRYSLER[/TD]
[TD]300C[/TD]
[TD][/TD]
[TD]38,741[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]2C3CDXDT[/TD]
[TD]2014[/TD]
[TD]DODGE[/TD]
[TD]CHARGER[/TD]
[TD]R/T[/TD]
[TD]19,063[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3C4PDCBG[/TD]
[TD]2014[/TD]
[TD]DODGE[/TD]
[TD]JOURNEY[/TD]
[TD][/TD]
[TD]20,295[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]3C4PDCBG[/TD]
[TD]2014[/TD]
[TD]DODGE[/TD]
[TD]JOURNEY[/TD]
[TD][/TD]
[TD]17,848[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]2FMDK3JC[/TD]
[TD]2013[/TD]
[TD]FORD[/TD]
[TD]EDGE[/TD]
[TD]SEL[/TD]
[TD]32,426[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]3GTP2VE3[/TD]
[TD]2011[/TD]
[TD]GMC[/TD]
[TD]SIERRA[/TD]
[TD][/TD]
[TD]46,409[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1C4NJDBB[/TD]
[TD]2014[/TD]
[TD]JEEP[/TD]
[TD]COMPASS[/TD]
[TD]4X[/TD]
[TD]17,835[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1C4NJDBB[/TD]
[TD]2014[/TD]
[TD]JEEP[/TD]
[TD]COMPASS[/TD]
[TD]4X[/TD]
[TD]24,456[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

Question 2
What is the name of the sheet where you want to copy and paste the row(s) matching the VINs that you'd be entering into the InputBox as the search criteria.

 

[Carguy37122]

I'm almost there but still lost. Two questions.

Question 1
What is the name of the sheet where you have this:

[TABLE="class: cms_table, width: 620"]
<tbody>[TR]
[TD]VIN# - 8 Digits
[/TD]
[TD]Year
[/TD]
[TD]Make
[/TD]
[TD]Model
[/TD]
[TD]Trim
[/TD]
[TD]Miles
[/TD]
[TD]# of Matches
[/TD]
[/TR]
[TR]
[TD]1G6DC5EY
[/TD]
[TD]2011
[/TD]
[TD]CADILLAC
[/TD]
[TD]CTS
[/TD]
[TD][/TD]
[TD]35,958
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]1G1PE5SB
[/TD]
[TD]2014
[/TD]
[TD]CHEVY
[/TD]
[TD]CRUZE
[/TD]
[TD][/TD]
[TD]23,176
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]1G1PG5SC
[/TD]
[TD]2012
[/TD]
[TD]CHEVY
[/TD]
[TD]CRUZE
[/TD]
[TD][/TD]
[TD]54,261
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]2G1WC5E3
[/TD]
[TD]2014
[/TD]
[TD]CHEVY
[/TD]
[TD]IMPALA
[/TD]
[TD][/TD]
[TD]19,713
[/TD]
[TD]38
[/TD]
[/TR]
[TR]
[TD]2G1WB5E3
[/TD]
[TD]2014
[/TD]
[TD]CHEVY
[/TD]
[TD]IMPALA
[/TD]
[TD][/TD]
[TD]13,949
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]2G1WB5E3
[/TD]
[TD]2014
[/TD]
[TD]CHEVY
[/TD]
[TD]IMPALA
[/TD]
[TD]LT
[/TD]
[TD]16,326
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]2G1125S3
[/TD]
[TD]2014
[/TD]
[TD]CHEVY
[/TD]
[TD]IMPALA
[/TD]
[TD]2LT
[/TD]
[TD]14,952
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]2G1WG5E3
[/TD]
[TD]2013
[/TD]
[TD]CHEVY
[/TD]
[TD]IMPALA
[/TD]
[TD][/TD]
[TD]27,506
[/TD]
[TD]42
[/TD]
[/TR]
[TR]
[TD]1G11H5SA
[/TD]
[TD]2013
[/TD]
[TD]CHEVY
[/TD]
[TD]MALIBU
[/TD]
[TD][/TD]
[TD]44,518
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]1C3CCBBG
[/TD]
[TD]2014
[/TD]
[TD]CHRYSLER
[/TD]
[TD]200
[/TD]
[TD][/TD]
[TD]18,647
[/TD]
[TD]23
[/TD]
[/TR]
[TR]
[TD]1C3CCBBG
[/TD]
[TD]2014
[/TD]
[TD]CHRYSLER
[/TD]
[TD]200
[/TD]
[TD]TOURING
[/TD]
[TD]16,613
[/TD]
[TD]23
[/TD]
[/TR]
[TR]
[TD]1C3CCBBG
[/TD]
[TD]2014
[/TD]
[TD]CHRYSLER
[/TD]
[TD]200
[/TD]
[TD]TOURING
[/TD]
[TD]28,845
[/TD]
[TD]23
[/TD]
[/TR]
[TR]
[TD]1C3CCBBG
[/TD]
[TD]2014
[/TD]
[TD]CHRYSLER
[/TD]
[TD]200
[/TD]
[TD]TOURING
[/TD]
[TD]18,025
[/TD]
[TD]23
[/TD]
[/TR]
[TR]
[TD]2C3CCAET
[/TD]
[TD]2012
[/TD]
[TD]CHRYSLER
[/TD]
[TD]300C
[/TD]
[TD][/TD]
[TD]38,741
[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD]2C3CDXDT
[/TD]
[TD]2014
[/TD]
[TD]DODGE
[/TD]
[TD]CHARGER
[/TD]
[TD]R/T
[/TD]
[TD]19,063
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]3C4PDCBG
[/TD]
[TD]2014
[/TD]
[TD]DODGE
[/TD]
[TD]JOURNEY
[/TD]
[TD][/TD]
[TD]20,295
[/TD]
[TD]27
[/TD]
[/TR]
[TR]
[TD]3C4PDCBG
[/TD]
[TD]2014
[/TD]
[TD]DODGE
[/TD]
[TD]JOURNEY
[/TD]
[TD][/TD]
[TD]17,848
[/TD]
[TD]27
[/TD]
[/TR]
[TR]
[TD]2FMDK3JC
[/TD]
[TD]2013
[/TD]
[TD]FORD
[/TD]
[TD]EDGE
[/TD]
[TD]SEL
[/TD]
[TD]32,426
[/TD]
[TD]28
[/TD]
[/TR]
[TR]
[TD]3GTP2VE3
[/TD]
[TD]2011
[/TD]
[TD]GMC
[/TD]
[TD]SIERRA
[/TD]
[TD][/TD]
[TD]46,409
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]1C4NJDBB
[/TD]
[TD]2014
[/TD]
[TD]JEEP
[/TD]
[TD]COMPASS
[/TD]
[TD]4X
[/TD]
[TD]17,835
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]1C4NJDBB
[/TD]
[TD]2014
[/TD]
[TD]JEEP
[/TD]
[TD]COMPASS
[/TD]
[TD]4X
[/TD]
[TD]24,456
[/TD]
[TD]7
[/TD]
[/TR]
</tbody>[/TABLE]

Question 2
What is the name of the sheet where you want to copy and paste the row(s) matching the VINs that you'd be entering into the InputBox as the search criteria.

Answer to question 1 - Sheet is named SELLER LIST

Answer to question 2 - Sheet is named Master Summary

Thanks Tom!

 

[Tom Urtis]

Thionking more about this, why do you need VBA in the first place? VINs by design are unique numbers. Why not put a lookup formula in the Master Summary sheet, enter your VIN in a cell and have the info from the Seller List sheet for that VIN be returned that way?

 

[Carguy37122]

I'm cool with any way that is easy to pull up the data.

I only reference the 1st 8 digits of the vin#. Is there a way to create a lookup where I can paste multiple vin#s, match the 1st 8 digits of the vin against the vin# in sales history (2014 sales matrix) and generate a list of transactions (rows) that match?

Usually, I'm looking up about 30- 40 vin#s at a time, but it is growing rapidly

(the big picture, I get the list of vin#s from my customers. Using these vin#s, I search the sales history to find customers who have purchased similar units (referencing the 1st 8 digits of the vin#) and call, email and fax the customers the information regarding the recent vehicles we are currently selling.

Unfortunately, this whole process is manual and takes me hours to finish the process. Every week the inventory changes, so its a repetitive process that drives me nuts. Lol

I figured there is a better way to do it and started researching it.

That's what led me to the forum and contacting you


Sorry for the long email.

Dave

 

[Carguy37122]

Tom,

Good Afternoon!

Hope you had a good weekend!

What about a worksheet_selection change for the seller list worksheet? Would that do the trick?