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
With .Cells(X, Y)
.Value = .Value
End With
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments