VBA paste as values - how to

Question

I have some VBA code that copys a range from one sheet and then pastes it to another at the first blank line. What it is copying are vlookup formulas so when it pastes it pastes all 0's, how would I go about getting it to paste what it copies as values so the results are retained?

Code:

Private Sub PasteChartDataQtyCompare()
'This step pastes the range of values to the chart data tab
    Sheets(1).Range("A6:J22").Copy _
    Destination:=Sheets("Chart Data").Cells(Sheets("Chart Data").Rows.Count, 1).End(xlUp).Offset(1, 0)
End Sub

Just set the ranges equal to eachother (note they have to be the same size I believe), like `Range([destination range]).Value = Range([copy from range]).Value` — BruceWayne, Jul 25 '18 at 21:37
You shouldn't be using destination. Use PasteSpecial xlPasteValues instead — Daniel Souza, Jul 25 '18 at 21:41
so would I just replace Destination with PasteSpecial xlPasteValues? I have never used that and am a bit confused as to where to add it in the code — user3496218, Jul 25 '18 at 21:42

score 4 · Accepted Answer · answered Jul 25 '18 at 22:07

4

Transfer the values directly bypassing the clipboard.

Private Sub PasteChartDataQtyCompare()
    'This step pastes the range of values to the chart data tab
    with workSheets(1).Range("A6:J22")
        workSheets("Chart Data").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).resize(.rows.count,.columns.count) = .value
    end with
End Sub

answered Jul 25 '18 at 22:07

Indeed, this is the more efficient approach. – jeffreyweir Jul 25 '18 at 22:11

score 2 · Answer 2 · answered Jul 25 '18 at 21:46

2

Use Range Method "Range.PasteSpecial xlPasteValue"

Example:

Sheets("Sheet1").Columns("A").Copy
Sheets("Sheet2").Columns("B").PasteSpecial xlPasteValues

answered Jul 25 '18 at 21:46

Tina Lo

21
3

score 1 · Answer 3 · answered Jul 25 '18 at 21:39

1

You want to use

.PasteSpecial xlPasteValues

A similar question was answered in detail here: Excel VBA Copy Paste Values only( xlPasteValues )

answered Jul 25 '18 at 21:39

hugotoledo_usa

21
3

thanks. I am not familiar with that, would I just add it to the end of the code? – user3496218 Jul 25 '18 at 21:43

VBA paste as values - how to

3 Answers3

Linked

Related