CODEWITHSUNDEEP
vbaexcel
Ang3lo
Ang3lo

Reputation: 77

Excel VBA - Paste as values

I'm trying to make a function that copies the information from one sheet to another but I'm struggling with the paste as values function. Do you know why it is not working?

Sub Copy_Data()
    Dim Src As Worksheet, Dst As Worksheet
    Dim LastRow As Long, r As Range
    Dim CopyRange As Range
    'Change these to the correct sheet names
    Set Src = Sheets("Sheet1")
    Set Dst = Sheets("Sheet2")
    LastRow = Src.Cells(Cells.Rows.Count, "A").End(xlUp).Row

    For Each r In Src.Range("A2:A" & LastRow)
        If (Month(r.Value) = "2" And Year(r.Value) = "1902") Then
            If CopyRange Is Nothing Then
                Set CopyRange = r.EntireRow
            Else
                Set CopyRange = Union(CopyRange, r.EntireRow)
            End If
        End If
    Next r

    If Not CopyRange Is Nothing Then
        CopyRange.Copy Dst.Range("A1").PasteSpecial(xlPasteValues)
    End If
End Sub

Upvotes: 1

Views: 900

Answers (4)

L42
L42

Reputation: 19727

This answer is correct but I'll just elaborate because this question has appeared tons of times here in SO. Eirikdaude is correct about using the correct syntax which is:

expression.Copy(Destination)

from MSDN Range Object Copy Method.
So this line will work:

Range("A1").Copy Destination:=Range("B1")

Destination argument requires a range where you want your data to be transferred.
Supplying this argument skips the clip board while omitting it will copy the range to the clip board (again as stated in the link).

Now, your code: CopyRange.Copy Dst.Range("A1").PasteSpecial(xlPasteValues) does not meet above requirements. Why?

  1. It doesn't meet the syntax where the Destination argument should be a range. Instead, you passed a Method of a Range Object which is PasteSpecial.
  2. You didn't omit the argument so the Range was not pass to clip board.

To make it work, you need to do it like Eirikdaude did which is separating the statements in two(2) lines.

CopyRange.Copy ' omit the argument so range is passed in clip board
Dst.Range("A1").PasteSpecial xlPasteValues ' execute PasteSpecial method

But he also added this line:

Application.CutCopyMode = False ' clears the clip board

That is used to clear the clip board. Remember that when you execute Copy without the Destination argument, you passed it to the clip board so you have to clear it (at least in my opinion).

You can have it in one line though like this:

CopyRange.Copy: Dst.Range("A1").PasteSpecial xlPasteValues

Above will work as well. Take note of the colon : after CopyRange.Copy.
That is a line (statement) separating character which indicates that what follows it is treated as a new line or in another perspective as a statement separator to indicate the end of an individual statement.

Upvotes: 1

Steven Martin
Steven Martin

Reputation: 3272

try

CopyRange.Copy 
Dst.Range("A1").PasteSpecial xlPasteValues

but you can also just do

 Dst.EntireRow.Value = CopyRange.EntireRow.Value

Upvotes: 1

SierraOscar
SierraOscar

Reputation: 17627

Or you could miss out the clipboard entirely:

If Not CopyRange Is Nothing Then
    Dst.Range("A1").Resize(CopyRange.Rows.Count, CopyRange.Columns.Count).Value = CopyRange.Value
End If

Upvotes: 3

eirikdaude
eirikdaude

Reputation: 3256

When using pastespecial you need to have the copy and paste statements in separate commands. Note that even if you were doing a "normal" paste, you'd not have .Paste after declaring the destination-range. I think it is also a good idea to set Application.CutCopyMode to false after the operation, though I am not positive about this.

The correct syntax would be something like:

If Not CopyRange Is Nothing Then
  CopyRange.Copy
  Dst.Range("A1").PasteSpecial xlPasteType:=xlPasteValues
  Application.CutCopyMode = False
End If

Upvotes: 1

Related Questions