找到你要的答案

Q:Copy (only value and not formula) range of cells to another sheet in same file

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
answer1: 回答1:

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

这重复你以前的努力,虽然在我看来,有一些含糊不清的目的地(s)的价值观。一排接受的价值观,你用的是我在第一次和countd但似乎没有他们之间的关系。

answer2: 回答2:

You need pastespecial:

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

你需要pastespecial:

copyRange.Copy
Cells(countD, 1).PasteSpecial Paste:=xlPasteValues
answer3: 回答3:

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