Code help - search / copy-paste

[Anthony]

Hi all,

being a bit of a novice and getting lots of help from this site I have tried
to write my own code, but as I am getting errors with it, I thought I would
ask for help....again !

Basically I want some code to search through column D of sheet
ReportDatabase for the date shown in cell D2 of sheet1.
Once this date is matched copy that row A:C and paste into row2 of the
DisplayReport sheet

any help appreciated

 

[Office_Novice]

Sub DoTonysStuff()
Dim ReportDBSheet As Worksheet
Dim DisplayReport As Worksheet
Dim FoundCell As Range
Dim SearhCell As Range
Dim SearchRange As Range
Dim FoundRange As Range
Dim x
Set ReportDBSheet = ActiveWorkbook.Worksheets(1)
Set DisplayReport = ActiveWorkbook.Worksheets(2)
Set SearhCell = ReportDBSheet.Range("D2")
Set SearchRange = ReportDBSheet.Range("D")
Set FoundCell = SearchRange.Find(What:=SearhCell.Value, LookIn:=xlValues)
x = 1

For Each FoundCell In SearchRange
If FoundCell = SearhCell Then
FoundCell.Copy Destination:=DisplayReport.Cells(x, 1)
x = x + 1
End If
Next
End Sub
This might take a little modifying but try:

 

[JLGWhiz]

This is untested:

Sub SeachnPaste()
Dim c As Range, myRange As Range, lr As Long
lr = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
myRange = ActiveSheet.Range("D3" & lr)
For Each c In myRange
If c.Value = Range("D2").Value Then
Range("A" & c.Row & ":C" & c.Row).Copy _
Sheets("DisplayReport").Range("A2")
End If
Next
End Sub

 

[Rick Rothstein \(MVP - VB\)]

Your write up makes it sound like there will be only one date in Column D on
the ReportDatabase worksheet that will match the date in D2 on Sheet1. If
that is the case, this code should do what you want...

Sub FindSingleDate()
Dim C As Range
With Worksheets("ReportDatabase")
Set C = .Columns("D").Find( _
What:=Worksheets("Sheet1").Range("D2").Value, _
After:=Range("D1"), LookAt:=xlWhole, LookIn:=xlValues)
If Not C Is Nothing Then
C.Offset(0, -3).Resize(1, 3).Copy .Range("A2")
End If
End With
End Sub

Rick