CODEWITHSUNDEEP
excelvba
user3496218
user3496218

Reputation: 209

VBA paste as values - how to

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

Upvotes: 0

Views: 30927

Answers (3)

user4039065
user4039065

Reputation:

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

Upvotes: 4

Tina Lo
Tina Lo

Reputation: 21

Use Range Method "Range.PasteSpecial xlPasteValue"

Example:

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

Upvotes: 2

hugotoledo_usa
hugotoledo_usa

Reputation: 21

You want to use 

.PasteSpecial xlPasteValues

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

Upvotes: 1

Related Questions