Read only Values from Range and copy to Destination:Sheets("MyData"). Cells(x,y)

Question

I have set a range

Dim vWorkingRange As Range

I'm reading my data as:

Set vWorkingRange = Range(Sheets("My Sheet").Cells(RowStart,  ColStart)),Sheets("My Sheet").Cells(RowFinish, ColFinish))

This is a single column of data. Now I want to paste the values

vWorkingRange.Copy Destination:=Sheets("My Sheet").Cells(myRow,myCol)

I'd like to read the values only from the source range as it may have a formula. But when I paste to my destination sheet I get the formula and not the value.

I cant seem to locate how to do this.

if it is a single column then won't both column references be the same i.e. both colStart or both ColFinish? — QHarr, Feb 23 '18 at 10:20
you want paste special method which means your paste will be on a different line from the copy. Paste as values. macro recorder will capture this if you perform the actions. — QHarr, Feb 23 '18 at 10:22
Possible duplicate. See here: https://stackoverflow.com/questions/23937262/excel-vba-copy-paste-values-only-xlpastevalues — QHarr, Feb 23 '18 at 10:22

score 0 · Answer 1 · answered Feb 23 '18 at 10:30

0

All you need is this...

vWorkingRange.Copy
Sheets("My Sheet").Cells(myRow, myCol).PasteSpecial xlPasteValues

answered Feb 23 '18 at 10:30

Subodh Tiwari sktneer

9,906
2
18
22

That did not work, I get a Compile Error Expected: End of statement message – P Marno Feb 23 '18 at 10:47
Check your code. Do you have a `With / End With` block in your code? It seems you forgot to include `End With`. – Subodh Tiwari sktneer Feb 23 '18 at 11:05

score 0 · Answer 2 · answered Feb 23 '18 at 10:46

0

a faster, and more reliable code could be:

With Sheets("My Sheet") ' reference wanted sheet
    With Range(.Cells(RowStart, ColStart), .Cells(RowFinish, ColFinish)) ' reference referenced sheet range
        .Parent.Cells(myRow, myCol).Resize(.Rows.Count, .Columns.Count).Value = .Value 'copy referenced range values only and paste them starting from referenced sheet cells(myRow, myCol)
    End With
End With

answered Feb 23 '18 at 10:46

DisplayName

13,283
2
11
19

This looks good however I'm only reading on value and not the full range back to the parent sheet. I could however maybe set up a loop and read it cell by cell, seems a bit over kill however. – P Marno Feb 23 '18 at 10:56
I understand you only get one cell pasted but that's not what's happening in my test. Please, check carefully your actual values of `RowStart, ColStart, RowFinish, ColFinish, myRow, myCol` before the last statements gets executed (setp through your code and use Immediate Window) – DisplayName Feb 23 '18 at 12:10

Read only Values from Range and copy to Destination:Sheets("MyData"). Cells(x,y)

2 Answers2