Hi,
Below code works for me as desired.. It gives me output in Cell Q2:Q.
But i need the out put in PASTE SPECIAL format? any good to approach this?
Code
Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
'What are the names of our worksheets?
Set sourceSheet = Worksheets("Sheet1")
Set outputSheet = Worksheets("Sheet2")
'Determine last row of source
With sourceSheet
SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With outputSheet
'Determine last row in col P
OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
'Apply our formula
.Range("Q2:Q" & OutputLastRow).Formula = _
"=VLOOKUP(A2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
End With
End Sub
Display More