How to paste VALUES ONLY in excel VBA?

Question

So I need to paste the values only of a range of data into the sheet "MasterList". I was looking for ways to do this and found this:

.PasteSpecial Paste:=xlPasteValues

How should I go about changing my code so that I can use the .PasteSpecial Paste:=xlPasteValues function? I'm thinking I need two lines of code to accomplish this but I can't figure out the syntax...

Dim Lastrow As Integer

Lastrow = Cells(15, 3).Value + 3

Dim Copyrange As String

Startrow = 3
Let Copyrange = "I" & Startrow & ":" & "K" & Lastrow
Range(Copyrange).Select
Selection.Copy

Sheets.Add.Name = "MasterList"
**ActiveSheet.Paste Destination:=Range("A1")**
ActiveSheet.Move

This is my first time seeing `Let` actually be explicitly included. VBA doesn't require you to write it, and nobody really ever does. From the MSDN: "Optional. Explicit use of the Let keyword is a matter of style, but it is usually omitted." — Toddleson, Jul 30 '21 at 15:19

score 1 · Answer 1 · answered Jul 30 '21 at 14:49

You should avoid using select but also avoid the clipboard as that is slow:

With ActiveSheet 'should put the actual sheet here
    Dim Lastrow As Long
    Lastrow = .Cells(15, 3).Value + 3
    
    Dim startrow As Long
    startrow = 3
    
    Dim Copyrange As String
    Copyrange = "I" & startrow & ":" & "K" & Lastrow
    
    Dim rng As Range
    Set rng = .Range(Copyrange)
    
    Dim mstlt As Worksheet
    Set mstlt = Worksheets.Add
    
    mstlt.Name = "MasterList"
    
    mstlt.Range("A1").Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
End With

How to paste VALUES ONLY in excel VBA?

1 Answers1