Copy and paste into another WB using a search function?

[Gareth]

Hi all you awesome Excel Ninja's!
WB = WorkBook
WS = WorkSheet
WB1 = Workbook where data is copied from
WB2 = WorkBook where data is pasted
I'm using Excel 2010

I am battling to get my head around this problem. Basically, a range of data is created manually then the macro is initiated and copies the range, then opening another WB, searching all the sheets for a date value (i have it formatted as DD-MM-YYYY and is in the initial WS) once it finds this entry, it will paste the range into the same sized range below that found date value. I first started on the route of using an hLookup function, but i couldn't get that to work then realised that the function is normally used to reference another cell based on the value it's looking for... and I don't know how to use the other search functions in VB code...

SO it should go something like this;

Sub CopyDta()

WB1 = ActiveWorkBook
WB2 = "file Path"
a = ActiveSheet.Range("A8").Value     'cell with formatted date value to look up
myRange = ActiveSheet.Range("D40:D64")    'range of cells to copy from

Workbooks.Open WB2

'the code I made here absolutely doesn't work, but basically I need to look for the value that
'is assigned to "a" in the range "D5:J5" on each WS in WB2. Once it finds this, the range of
'row numbers 81:105 below (offset(76,0)) is where myRange should be pasted to. The data is
'numbers and will need to be formatted to show the values only but only to 2 decimal places.

WB2.close SaveChanges.True
WB1.close SaveChanges.True

Exit Sub

 

[Luke M]

We can use the native Find method. Something like this...

Sub CopyDta()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws As Worksheet
Dim filePath As String
Dim searchValue As Variant
Dim myRange As Range
Dim fCell As Range

filePath = "C:\My documents\My Book.xls"
Set wb1 = ActiveWorkbook
Set myRange = ActiveSheet.Range("D40:D64")
searchValue = ActiveSheet.Range("A8").Value

Application.ScreenUpdating = False
'Open wb2
Set wb2 = Workbooks.Open(filePath)

'Search and find
Set fCell = Nothing
For Each ws In wb2.Worksheets
    Set fCell = ws.Cells.Find(searchValue)
    'Stop when we find the value
    If Not fCell Is Nothing Then
        myRange.Copy
        fCell.Offset(1).PasteSpecial (xlPasteValues)
        Selection.NumberFormat = "#.00"
        Exit For
    End If
Next ws

wb2.Close True
wb1.Close True 'You sure you want to do this?

Application.ScreenUpdating = True
End Sub