VBA - Import range from other worksheet-CodePudding

I'm new to VBA and I'm trying to move my first steps into it, I need help!

Below you will find a code to import a range from a selected file to the active workbook. The macro is assigned to a button on the active workbook.

What I miss, and I'm not able to write is that I would like to paste the Range("U2:AH2") on the row of the active worksheet where the value of cell T2 (cell T2 is in the opened file) match a value in column D of the table in the active worksheet.

Sub Import_QTN_Data()

Dim FileToOpen As Variant
Dim OpenBook As Workbook

Application.ScreenUpdating = False

FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen <> False Then
    Set OpenBook = Application.Workbooks.Open(FileToOpen)
    OpenBook.Worksheets("QUOTATION").Range("U2:AH2").Copy
    ThisWorkbook.Worksheets("QUOTATION").Range("E30").PasteSpecial xlPasteValues, skipblanks:=True

    OpenBook.Close False
End If

Application.ScreenUpdating = True

End Sub

Hope that somebody could help me in write this part of the code. Many thanks in advance

Luca

CodePudding user response：

You could use Match(), or Find(). Here's an example using Match()

Sub Import_QTN_Data()

    Dim FileToOpen As Variant
    Dim OpenBook As Workbook, wsQuote As Worksheet, m
    
    Application.ScreenUpdating = False
    
    FileToOpen = Application.GetOpenFilename( _
                  Title:="Browse for your File & Import Range", _
                  FileFilter:="Excel Files (*.xls*),*xls*")
    If FileToOpen <> False Then
        Set wsQuote = ThisWorkbook.Worksheets("QUOTATION")
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        With OpenBook.Worksheets("QUOTATION")
            'use Match() on ColD
            m = Application.Match(.Range("T2").Value, wsQuote.Columns("D"), 0)
            If Not IsError(m) Then       'got a match (`m` is not an error value)
                .Range("U2:AH2").Copy
                wsQuote.Cells(m, "E").PasteSpecial xlPasteValues, skipblanks:=True
            End If
        End With
        OpenBook.Close False
    End If
    
    Application.ScreenUpdating = True

End Sub