# Q：复制（只值和不公式）范围内的单元格到另一个表在同一文件

I am using the following code. This code copies the cell formulas into the other sheet. Does anyone know how can I modify it to just copy the cell values and not formulas?

``````sub copyRangeOver()

Dim i As Integer
i = 631

Dim copyRange  As Range
Set copyRange = ThisWorkbook.Worksheets("Coupling xyz data").Range("J" & 1 & ":V" & i)

Dim countD As Integer
countD = 1
copyRange.Copy Destination:=Cells(countD, 1)

Dim j As Integer
j = 466
Set copyRange = ThisWorkbook.Worksheets("Spring xyz data").Range("J" & 1 & ":V" & j)

copyRange.Copy Destination:=Cells(i + 1, 1)

End Sub
``````

``````sub copyRangeOver()

Dim i As Integer
i = 631

Dim copyRange  As Range
Set copyRange = ThisWorkbook.Worksheets("Coupling xyz data").Range("J" & 1 & ":V" & i)

Dim countD As Integer
countD = 1
copyRange.Copy Destination:=Cells(countD, 1)

Dim j As Integer
j = 466
Set copyRange = ThisWorkbook.Worksheets("Spring xyz data").Range("J" & 1 & ":V" & j)

copyRange.Copy Destination:=Cells(i + 1, 1)

End Sub
``````

You can do a direct value transfer which is faster than a Copy and Paste Special, Values and doesn't involve the clipboard.

``````Sub copyRangeOver()

Dim i As Long, j As Long, countD As Long
Dim copyRange As Range

i = 631
countD = 1

With ThisWorkbook.Worksheets("Coupling xyz data")
Set copyRange = .Range("J" & 1 & ":V" & i)
Cells(countD, 1).Resize(copyRange.Rows.Count, copyRange.Columns.Count) = _
copyRange.Value
End With

j = 466

With ThisWorkbook.Worksheets("Spring xyz data")
Set copyRange = .Range("J" & 1 & ":V" & j)
Cells(i + 1, 1).Resize(copyRange.Rows.Count, copyRange.Columns.Count) = _
copyRange.Value
End With

End Sub
``````

That duplicates your previous effort although it seems to me that there is some ambiguity in the destination(s) of the values. For a row to receive the values, you are using i in the second one and CountD in the first but there doesn't appear to be a relationship between them.

``````Sub copyRangeOver()

Dim i As Long, j As Long, countD As Long
Dim copyRange As Range

i = 631
countD = 1

With ThisWorkbook.Worksheets("Coupling xyz data")
Set copyRange = .Range("J" & 1 & ":V" & i)
Cells(countD, 1).Resize(copyRange.Rows.Count, copyRange.Columns.Count) = _
copyRange.Value
End With

j = 466

With ThisWorkbook.Worksheets("Spring xyz data")
Set copyRange = .Range("J" & 1 & ":V" & j)
Cells(i + 1, 1).Resize(copyRange.Rows.Count, copyRange.Columns.Count) = _
copyRange.Value
End With

End Sub
``````

You need pastespecial:

``````copyRange.Copy
Cells(countD, 1).PasteSpecial Paste:=xlPasteValues
``````

``````copyRange.Copy
Cells(countD, 1).PasteSpecial Paste:=xlPasteValues
``````

This is normally how it's done.

``````  copyRange.Copy
Cells(i + 1, 1).PasteSpecial xlPasteValues
application.cutcopymode=0
``````

``````  copyRange.Copy
Cells(i + 1, 1).PasteSpecial xlPasteValues
application.cutcopymode=0
``````
excel  vba  excel-vba  excel-formula