Patriot2879
Well-known Member
- Joined
- Feb 1, 2018
- Messages
- 1,259
- Office Version
- 2010
- Platform
- Windows
hi I have the code below where I have added a vlookup, but once the cells are updated I want to remove the vlookup, I have tried '.value=.value' but that didn't work can you help please?
Code:
Private Sub CommandButton1_Click()
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Set copySheet = Worksheets("Jun")
Set pasteSheet = Worksheets("Jun")
Set r = Range("AP2", Range("AP" & Rows.Count).End(xlUp))
Range("A2").Resize(r.Rows.Count).Value = r.Value
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Range("AJ2:AJ" & Lastrow).SpecialCells(xlBlanks).Value = Range("AJ2").Value
On Error GoTo 0
Range("AJ2", Range("AJ2").End(xlDown)).Copy Range("I2")
Range("AB2", Range("AB2").End(xlDown)).Copy Range("E2")
Range("AD2", Range("AD2").End(xlDown)).Copy Range("F2")
Range("AO2", Range("AO2").End(xlDown)).Copy Range("G2")
Range("AG2", Range("AG2").End(xlDown)).Copy Range("H2")
Range("AS2", Range("AS2").End(xlDown)).Copy Range("M2")
Range("C2:C" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:D,3,FALSE)"
Range("B2:B" & Lastrow).Formula = "=IF(A2=A1,0,1)"
Range("D2:D" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:E,4,FALSE)"
Range("J2:J" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:K,10,FALSE)"
Range("K2:K" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:L,11,FALSE)"
Range("L2:L" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:M,12,FALSE)"
Range("N2:N" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:O,14,FALSE)"
End Sub