how to paste only values in excel

Question

I'm quite new to Excel and completely new to this forum.

I have taken the code from the below forum and modified it to my need.

http://pressf1.pcworld.co.nz/showthread.php?90122-Creating-Macro-to-copy-and-paste-data-into-the-next-empty-column.

Sub copyTotals()
    Dim TargetSht As Worksheet, SourceSht As Worksheet, SourceRow As Integer, SourceCells As Range
    Set SourceSht = ThisWorkbook.Sheets("DUN - Jan")
    Set TargetSht = ThisWorkbook.Sheets("DUN Jan - Jan,Feb,Mar,Apr")
    Set SourceCells = SourceSht.Range("L36,N36")
    If TargetSht.Range("C11").Value = "" Then
        SourceRow = 1
    ElseIf TargetSht.Range("C41") <> "" Then
        MsgBox ("The sheet is full, you need to create a new sheet")
    Else
        SourceRow = TargetSht.Range("C41").End(xlUp).Row + 1
    End If

    SourceCells.Copy TargetSht.Cells(SourceRow, 3)
End Sub

The problem is that the values pasted have the formating of the source and i only want to paste the values.

Can someone please help me with this.

1. the link is broken 2. You can make everyone's life easier by pasting the code directly in your question. — assylias, Jan 03 '13 at 08:51

score 4 · Accepted Answer · answered Jan 03 '13 at 08:56

4

Use .Copy together with .PasteSpecial. Instead of:

SourceCells.Copy TargetSht.Cells(2, SourceCol)

Do this:

SourceCells.Copy
TargetSht.Cells(2, SourceCol).PasteSpecial xlPasteValues

answered Jan 03 '13 at 08:56

Dan

10,480
23
49

Thanks... That is it :) Appreciate the help – maldivianGeek Jan 03 '13 at 09:00

score 2 · Answer 2 · answered Jan 03 '13 at 23:30

2

Using the macro recorder yields this kind of thing. I use it a lot when stuck.

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

answered Jan 03 '13 at 23:30

tony goodwin

64
6

SJC · Answer 3 · 2014-10-02T13:09:47.220

You don't need to copy and paste to do this (at least in Excel 2010 and above, I think). You just set the .Value of the range to equal itself. eg:

rng.Value = rng.Value

or

Sheet1.Range("A1:A10").Value = Sheet1.Range("A1:A10").Value

since .Value "Returns or sets a Variant value that represents the value of the specified range."

Note that this just works for values, not formats.

http://msdn.microsoft.com/en-us/library/office/ff195193(v=office.15).aspx

ufosnowcat · Answer 4 · 2013-01-03T08:59:13.943

0

right click in the cell and select paste special then you can choose values only

edit: for macros its the same principle use .PasteSpecial xlPasteValues like here

Set rng6 = .Range("A3").End(xlDown).Offset(0, 41)
rng6.Copy
ActiveSheet.Paste Destination:=Worksheets("Positions").Range("W2")

edited Jan 03 '13 at 08:59

answered Jan 03 '13 at 08:51

ufosnowcat

558
2
13

Link fixed - sorry about that – maldivianGeek Jan 03 '13 at 08:53

how to paste only values in excel

4 Answers4

Linked