Snasui Excel Forum

Code: Select all

If Range("A6").Value <> "" Then
    Application.Goto Reference:="OFFSET(R6C1,0,5,1,5)"
       Selection.Copy
  ThisWorkbook.Activate
    Application.Goto Reference:= _
        "OFFSET('[DataBase.xlsx]Sheet1'!R1C1,MATCH(R6C1,INDEX('[DataBase.xlsx]Sheet1'!R2C1:R50000C1,0),0),37)"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
      ThisWorkbook.Activate
   End If

Code: Select all

Dim rngSourceAll As Range, rngSource As Range
Dim rngTargetAll As Range, i As Integer
With ThisWorkbook.Worksheets("M011")
    Set rngSourceAll = .Range("a6", .Range("a" & .Rows.Count).End(xlUp))
End With
With Workbooks("Database.xlsx").Worksheets("Sheet1")
    Set rngTargetAll = .Range("a3", .Range("a" & .Rows.Count).End(xlUp))
End With
For Each rngSource In rngSourceAll
    If Application.CountIf(rngTargetAll, rngSource) > 0 Then
        i = Application.Match(rngSource, rngTargetAll, 0)
        rngTargetAll(i).Offset(0, 37).Resize(1, 5).Value = rngSource.Offset(0, 5).Resize(1, 5).Value
    End If
Next rngSource