从具有错误 #REF 不是值的多个工作表粘贴值

Question

我对 VBA 代码不粘贴值有问题

代码如下

Set rngSrc = .Range("D29")
rngSrc.Copy Destination:=rngDst

问题是我只收到#REF错误，因为 VBA 尝试粘贴单元格中的任何内容，但我希望它粘贴特殊值，但我无法使其与粘贴特殊功能一起使用。

有人知道如何让它工作吗？

Answer 1

你快到了，但你应该使用PasteSpecial而不是Destination方法，检查下面的这个漂亮的代码：

Private Sub copypaste()
Dim rngSrc As Range
Dim rngDest As Range
Dim ws As Worksheet: Set ws = Sheets("Your sheet name")
Set rngSrc = ws.Range("D29")
Set rngDest = ws.Range("A33") ' for example
rngSrc.Copy
rngDest.PasteSpecial xlPasteValues
End Sub

我觉得代码是不言自明的。不过，如果您有任何疑问，请告诉我

Answer 2

您的CombineDataFromAllSheets代码应该是：

Public Sub CombineDataFromAllSheets()
Dim wksSrc As Worksheet, wksDst As Worksheet
Dim rngSrc As Range, rngDst As Range
Dim lngLastCol As Long, lngSrcLastRow As Long, lngDstLastRow As Long
Set wksDst = ThisWorkbook.Worksheets("Import")
For Each wksSrc In ThisWorkbook.Worksheets
'These need updating on each pass of the loop.
'Set them at the start, rather than before the loop
'and at the end of the loop.
lngDstLastRow = LastOccupiedRowNum(wksDst)
lngLastCol = LastOccupiedColNum(wksDst)
Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)
If wksSrc.Name <> "Import" Then
lngSrcLastRow = LastOccupiedRowNum(wksSrc)
With wksSrc
Set rngSrc = .Range("D29")
rngSrc.Copy
rngDst.PasteSpecial xlPasteValues
End With
End If
Next wksSrc
End Sub