CODEWITHSUNDEEP
excelvba
VBA Noob
VBA Noob

Reputation: 33

VBA Copy and Paste after Vlookup is performed

Is there a more efficient way to copy and paste value after the vlookup code is executed? The idea is that after the vlookup is executed in almost 10,000+ cells I want to copy and paste the values as it makes the excel file more stable. 

Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
Dim X As Long
Dim Z As Long

'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 C
  OutputLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
 For Y = 17 To 28 'Q to AB
  For X = 2 To OutputLastRow
   If InStr(1, .Range("C" & X), "PO Materials") + InStr(1, .Range("C" & X), "PO Labor") > 0 And    Cells(2, Y) = "Forecast" Then
 'Apply  formula
 .Cells(X, Y).Formula = _
   "=VLOOKUP($E" & X & ",'" & sourceSheet.Name & "'!$A$2:$L$" & SourceLastRow & ",Match(" & Cells(1, Y).Address & ",'" & sourceSheet.Name & "'!$A$1:$AD$1,0),0)"
   .Cells(X, Y).Select
   .Cells(X, Y).Copy
   .Cells(X, Y).PasteSpecial Paste:=xlPasteValues
End If
 Next
Next

End With
End Sub

Upvotes: 0

Views: 6520

Answers (2)

VBA Noob
VBA Noob

Reputation: 33

Change .Formula to .Value so VBA directly executes the vlookup and then pastes the value

  .Cells(X, y).Value = _
  Evaluate("=VLOOKUP($E" & X & ",'" & sourceSheet.Name & "'!$A$2:$L$" & SourceLastRow & ",Match(" & Cells(1, y).Address & ",'" & sourceSheet.Name & "'!$A$1:$AD$1,0),0)")

Upvotes: 0

SierraOscar
SierraOscar

Reputation: 17637

With .Cells(X, Y)
    .Value = .Value
End With

Upvotes: 1

Related Questions