Hi Ivan,
Here is my code. I am looking to search for a row in worksheet 2 corresponding to a value entered in worksheet 1. If the value matches, it will copy the rows to worksheet 1. So far I discovered that while debugging it does search right; but is unable to copy. Can you please help me?
Sub Value_Proposition_NewSearch()
'
' Value_Proposition_NewSearch Macro
'
'
Dim wksTarget As Worksheet, wksSource As Worksheet
Dim rngTarget As Range, rngSource As Range, rngID As Range
Dim iD As String
Set wksTarget = ThisWorkbook.Sheets("Menu")
Set wksSource = ThisWorkbook.Sheets("ValuePropositionData")
Set rngID = wksTarget.Range("a24")
'clear any old data on summary sheet
Set rngTarget = rngID.SpecialCells(xlCellTypeLastCell)
If rngTarget.Row > rngID.Row Then
wksTarget.Range(rngID.Offset(1), rngTarget).EntireRow.ClearContents
End If
'matching rows will get copied to summary sheet starting row 3
Set rngTarget = rngID.Offset(25)
'find matching rows on address sheet starting row 2
Set rngSource = wksSource.Range("a9")
iD = rngID.Value
Do Until (rngSource.Value = "")
If rngSource.Value = iD Then
Application.CutCopyMode = True
rngSource.EntireRow.Copy
wksTarget.Paste rngTarget
Application.CutCopyMode = False
Set rngTarget = rngTarget.Offset(1)
End If
Set rngSource = rngSource.Offset(1)
Loop
End Sub