0

I have this sheet where many cells have a numeric value (ex: 304 550.07) but that actually depend on the value of other cells in the same sheet. For example: "D1 = C1+1" is what can be seen in the 'Formula bar' and "2" is the actual numerical value of the D1 cell.

I've looked up a way to copy the column I needed (D) and paste it on another sheet, here's the code (not mine):

Sub sbCopyRangeToAnotherSheet()
'Method 1
Sheets("Sheet1").Range("D1:D10").Copy Destination:=Sheets("Sheet2").Range("A1")

'Method 2
'Copy the data
Sheets("Sheet1").Range("D1:D10").Copy
'Activate the destination worksheet
Sheets("Sheet2").Activate
'Select the target range
Range("A1").Select
'Paste in the target destination
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

So, these two methods work but only if the values of the cells are numerical (locked?) and not if they depend on the value of another cell. As a result, when I try to copy the D column from 'Sheet1' to 'Sheet2', all I get is either:

=#REF!

OR

the calculations that were defined in the Formula bar in 'Sheet1', which means that it takes the values of the depending cell for 'Sheet2'.

All that to say that, I'm trying to find a way to copy the actual value that are written in the cell and not the calculations that lead to the result.

PS: Really sorry for those bad explanations, first post tho ;) Thanks.

YowE3K
  • 23,852
  • 7
  • 26
  • 40
T.Roustan
  • 1
  • 3
  • 1
    Check this out since this is answered a few times before https://stackoverflow.com/questions/23937262/excel-vba-copy-paste-values-only-xlpastevalues – krib Jul 12 '17 at 07:18

3 Answers3

1

To copy & paste only values you should use something like this in your code:

'copy range
Sheets("Sheet1").Range("D1:D10").Copy
'paste only values 
Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues
Krystian Kozłowski
  • 162
  • 3
0

If you are only trying to assign values of some cells to be the values of some other cells, and you don't want to copy formats, formulas, etc, just use the .Value property of the cells:

Sub sbCopyRangeToAnotherSheet()
    Sheets("Sheet2").Range("A1:A10").Value = Sheets("Sheet1").Range("D1:D10").Value
End Sub

This avoids all the issues inherent in using the clipboard as an intermediary in the transfer process.

YowE3K
  • 23,852
  • 7
  • 26
  • 40
0
Sheets.Range("D1:D10").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet2.Range("A1")
Maximuuuuuus
  • 46
  • 5