i have tried to make a code to fill a column with vlookup formula but it doesn't fill until end of the used row. i am not very good in coding, hope any expert here can help to correct my code.
The steps i am trying to do is:
1) In "working file.xlsm" copy the raw data from another workbook "Raw.xls" into "Sheet1"
2) fill in column H by vlookup formula from sheet "Mst"
I placed my command button at sheet "Mst".
Private Sub CommandButton1_Click()
Dim OpenFileName As String
Dim wb As Workbook
OpenFileName = Application.GetOpenFilename("clients saved spreadsheet,*.xls;*.xlsx")
If OpenFileName = "False" Then Exit Sub
Set wb = Workbooks.Open(OpenFileName)
wb.Sheets("Sheet1").UsedRange.Copy
ThisWorkbook.Sheets("Sheet1").Range("A1").PasteSpecial
ThisWorkbook.Sheets("Sheet1").Range("H2:H" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "=VLOOKUP($C2,Mst!$A$1:$D$2000,4,0)"
wb.Application.CutCopyMode = False
wb.Close SaveChanges:=False
End Sub
Bookmarks