Code help - search / copy-paste

  • Thread starter Thread starter Anthony
  • Start date Start date
A

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
 
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: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:
 
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:D" & 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
 
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
 
Back
Top