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
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