CODEWITHSUNDEEP
vbaexcel
Matt Ridge
Matt Ridge

Reputation: 3651

Pasting value only, Excel VBA

I have this script that I had help with already, but now comes an issue. I am attempting to paste only the value, not the formula that is inside the cell to another cell.

I thought placing the .Value at the end of formula would tell the script to paste only the value... it seems not to be. Can someone give me a suggestion on how to make this work?

Option Explicit

    Sub ONJL()
        Dim lastrow As Long
        Dim wsPAR As Worksheet 'PAERTO
        Dim wsRD As Worksheet 'Raw Data
        Dim wsTEM As Worksheet 'Archive

        Set wsPAR = Sheets("PAERTO")
        Set wsRD = Sheets("Raw Data")
        Set wsTEM = Sheets("Template")


        With wsRD
            Application.ScreenUpdating = False
            lastrow = .Range("J" & .Rows.Count).End(xlUp).Row
            wsRD.Range("J" & lastrow + 1).Formula = Date
            wsRD.Range("B2").Copy wsRD.Range("K" & lastrow + 1).Value
            wsRD.Range("B3").Copy wsRD.Range("L" & lastrow + 1).Value
            wsRD.Range("E2").Copy wsRD.Range("M" & lastrow + 1).Value
            wsRD.Range("E3").Copy wsRD.Range("N" & lastrow + 1).Value
            wsRD.Range("H2").Copy wsRD.Range("O" & lastrow + 1).Value
            wsRD.Range("H3").Copy wsRD.Range("P" & lastrow + 1).Value
            wsRD.Range("Q1:T1").Copy wsRD.Range("Q" & lastrow + 1)
            Application.ScreenUpdating = False
        End With
    End Sub

Upvotes: 0

Views: 3694

Answers (1)

Jon Crowell
Jon Crowell

Reputation: 22320

You can "copy" without actually using .Copy like this:

Sub CopyWithoutCopying()
    Dim wsRD As Worksheet
    Dim lastrow As Long

    Set wsRD = Sheets("Raw Data")

    With wsRD
        lastrow = .Range("J" & .Rows.Count).End(xlUp).Row
        .Range("K" & lastrow + 1).Value = .Range("B2").Value
        .Range("L" & lastrow + 1).Value = .Range("B3").Value
        ' etc...
    End With
End Sub

This approach doesn't use your clipboard, performs better, and doesn't select anything. And as Jimmy points out, you don't need the wsRD prefix inside the With block.

Upvotes: 5

Related Questions