Copy rows from one sheet to another based on value

pmich

Active Member
Joined
Jun 25, 2013
Messages
265
Try this code to copy one row of data which matches with your criteria.
Code:
Private Sub cmdSerachInfoBasedOnCriteriaCopyRowToAnotherSheet_Click()
 Dim UsrCriteria As Long 'if criteria is a string edit this accordingly
 Dim varCriteriaRowNum As Long
 Dim Src As Worksheet
 Dim Rpt As Worksheet
 Dim CopRng As Range
 Set Src = Sheets("Source")
 Set Rpt = Sheets("Report")
 Src.Select
 '''''''''''''''''''''' Pin Point Search begins ''''''''''''''''''''''''''''''''''''''''
 Dim rSrchCriteria As Range 'range to check whether Criteria is available
 Range("A1").Select 'Col A has Criteria in Src
 UsrCriteria = InputBox("Type the Criteria" & vbCrLf & _
   "to search :", "Criteria!")
 Set rSrchCriteria = Range("A:A").Find(UsrCriteria, , xlValues, xlWhole)
 'code below searches Citeria, only in the column specified above as Selected Range.
 If rSrchCriteria Is Nothing Then 'if Criteria is 38, & no record is matching, _
  then, 38 is not found.
  MsgBox "You typed: " & UsrCriteria & "." & vbCrLf & _
   "This is not FOUND." & vbCrLf & _
   "So, Nothing to copy!"
   Exit Sub
 Else 'rSrchCriteria Is Nothing 'if Criteria is 38, _
  record found may be 388. So, moving pointer to that record.
  With rSrchCriteria
   .Activate 'Pointer moved to that record.
  End With
  If Val(rSrchCriteria) <> Val(UsrCriteria) Then 'if User Criteria is 38, _
   Srch Criteria found may be 388. So, 38 is not found.
   MsgBox "You typed: " & UsrCriteria & "." & vbCrLf & _
   "This is not FOUND." & vbCrLf & _
   "So, Nothing to copy!"
   Exit Sub
  ElseIf Val(rSrchCriteria) = Val(UsrCriteria) Then 'if Criteria is 38 and _
   record found is 38, then 38 is found.
   varCriteriaRowNum = ActiveCell.Row 'Store Row Criteria
  End If 'Val(rSrchCriteria) <> Val(UsrCriteria)
 End If 'rSrchCriteria Is Nothing
  '''''''''''''''''''''' Pin Point Search ends ''''''''''''''''''''''''''''''''''''''''
  Rpt.Cells.ClearContents 'Erases all rows in Report Sheet. If you dont want to erase all rows, edit A2 given below. 
  Set CopRng = Src.Range("A" & Trim(Str(varCriteriaRowNum))).EntireRow
  CopRng.Copy Rpt.Range("A2") 'Copies the searched row in Report Sheet 'If you want to paste on a specific row, edit A2.
  Rpt.Select
End Sub
You have not mentioned whether you want to search some other criteria or not.
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,164
Office Version
2013
Platform
Windows
What criteria ?
Where do we match the criteria ?
Which Sheet is the row copied to ?
The link doesn't really provide much to go on !!
 

Forum statistics

Threads
1,081,728
Messages
5,360,923
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top