Find string in row. Offset, Copy then Paste

[iaindowner]

I need to search for a string located in sheet1 cell A2 in row A of sheet2. If the string is contained in the row then I need to select the cells offset (60,-1) and copy them and paste them in to the range on sheet1 B2:B61 matching the destination formatting. Is anyone able to help

 

[JLGWhiz]

There is no row A. There is a column A. But even if you mean column A, you cannot use Offset(60, -1) because there is no negative offset of column A. If you mean Row 1, vs column A, then the offset(60, -1) will be a single cell on Row 61, one column to the left of the found string. But your destination range indicates that you intended to copy a range of cells, probabaly from the found cell, down through row 61. So, if you want to find the value of sheet1!A2 on Row 1, Sheet2 and copy cells in rows 2:61 of that column, try this.

Sub cpy()
Dim sh1 As Worksheet, sh2 As Worksheet, lc As Long, fLoc As Range
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
lc = sh2.Cells(1, Columns.Count).End(xlToLeft).Column
Set fLoc = sh2.Range("A1", sh2.Cells(1, lc)).Find(sh1.Range("A2").Value, , xlValues, xlWhole)
    If Not fLoc Is Nothing Then
        fLoc.Offset(1, 0).Resize(60, 1).Copy sh1.Range("B2:B61")
    End If
End Sub

 

[iaindowner]

Yes that is what I'm trying to do and this works although I want to copy the cells in both columns. E.g. If there is a match in Cell B1 then I want to copy A2:B61 and not just B2:B61. Thanks for your help.

 

[JLGWhiz]

This should do it.

Sub cpy()
Dim sh1 As Worksheet, sh2 As Worksheet, lc As Long, fLoc As Range
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
lc = sh2.Cells(1, Columns.Count).End(xlToLeft).Column
Set fLoc = sh2.Range("A1", sh2.Cells(1, lc)).Find(sh1.Range("A2").Value, , xlValues, xlWhole)
    If Not fLoc Is Nothing And fLoc.Column <> "A" Then
        fLoc.Offset(1, -1).Resize(60, 2).Copy sh1.Range("B2")
    End If
End Sub