Excel VBA - Paste as values

Question

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

SierraOscar · Accepted Answer · 2015-03-27 23:11:34Z

3

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

answered Mar 27, 2015 at 23:11

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

eirikdaude · Accepted Answer · 2015-03-27 23:06:33Z

1

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

answered Mar 27, 2015 at 23:06

eirikdaude

3,2586 gold badges29 silver badges58 bronze badges

Comments

Steven Martin · Accepted Answer · 2015-03-27 23:12:23Z

1

try

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

but you can also just do

 Dst.EntireRow.Value = CopyRange.EntireRow.Value

edited Mar 27, 2015 at 23:12

answered Mar 27, 2015 at 23:02

Steven Martin

3,3201 gold badge23 silver badges29 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:22:11Z

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?

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.
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.

Collectives™ on Stack Overflow

Excel VBA - Paste as values

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related