Copy paste as values Excel VBA

Question

I have this code but I need it to copy paste the data as values. I know it probably is very simple, but I have not used .Paste function, so I do not know how to convert it. I am at a very beginner level. Many thanks in advance!

Sub Movetabletototal()
    Dim Count As Integer
    Dim copyRng As Range, pasteRng As Range
    Dim totalWS As Worksheet, mixerWS As Worksheet

    Set totalWS = Worksheets("TOTAL")
    Set mixerWS = Worksheets("MIXER TOTAL")

    Set copyRng = mixerWS.Range("P3:Q" & mixerWS.Cells(mixerWS.Rows.Count, 17).End(xlUp).Row)

    Dim newRow As Long
    newRow = totalWS.Cells(totalWS.Rows.Count, 1).End(xlUp).Row
    If newRow > 1 Then newRow = newRow + 1

    copyRng.Copy totalWS.Range(totalWS.Cells(newRow, 1), totalWS.Cells(newRow + copyRng.Rows.Count, copyRng.Columns.Count))
End Sub

Read PasteSpecial Method.

M--
– M--

2017-05-11 13:52:22 +00:00
Commented May 11, 2017 at 13:52 — M--
– M--, Commented May 11, 2017 at 13:52

David Zemens · Accepted Answer · 2017-05-11 14:02:28Z

2

If you're only interested in the values there's no reason to use either Copy, Paste or PasteSpecial. Instead, just use a direct value assignment:

Dim destinationRange as Range
With totalWs
    Set destinationRange = .Cells(newRow, 1).Resize(copyRange.Rows.Count, copyRange.Columns.Count)
End With

destinationRange.Value = copyRange.Value

answered May 11, 2017 at 14:02

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

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

Comments

M-- · Accepted Answer · 2017-05-11 14:03:46Z

1

You need to use PasteSpecial in your code. Change the last line to this:

'.
'. Your code will be the same till here
'.

copyRng.Copy

Set pasteRng = totalWS.Range(totalWS.Cells(newRow, 1), _
totalWS.Cells(newRow + copyRng.Rows.Count, copyRng.Columns.Count))

pasteRng.PasteSpecial xlPasteValues

End Sub

Or you can use values like below (set the ranges like above):

pasteRng.Value = copyRng.Value

edited May 11, 2017 at 14:03

answered May 11, 2017 at 13:58

M--

33.7k12 gold badges74 silver badges115 bronze badges

2 Comments

Vinnie Over a year ago

How about using .value=.value instead copy-paste?

M-- Over a year ago

@Vinnie OP asks about using paste function. I personally prefer that method (Just check out my last question here and my own answer to it).

Chris · Accepted Answer · 2017-05-11 15:07:07Z

0

You could also try

.PASTESPECIAL (-4163)

answered May 11, 2017 at 15:07

Chris

2541 silver badge5 bronze badges

1 Comment

M-- Over a year ago

xlPasteValues = -4163 --> msdn.microsoft.com/en-us/library/office/ff837425.aspx

Collectives™ on Stack Overflow

Copy paste as values Excel VBA

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related