excel - 仅使用 VBA 粘贴值：错误“无法获取 Range 类的 PasteSpecial 属性

Question

我试图让一个小宏工作。基本上，我想要改变的只是让它只粘贴值。

我没有写大部分代码，只写了几个片段。它是这样的：

Private Sub CommandButton1_Click()

Dim LRSrc As Long, LRDest As Long, SrcRng As Range

With Sheets("Input sheet")
    LRSrc = .Cells(.Rows.Count, 1).End(xlUp).Row 'assumes column 1 is contiguous
    Set SrcRng = .Range("A2:G" & LRSrc)
End With
If WorksheetFunction.CountA(Range("A2:G2")) = 0 Then
Dim emptyErr As Integer
emptyErr = MsgBox("Data missing or incomplete! Please type in all the fields, starting from row 2", vbOKOnly, "No data")

Else

With Sheets("General inventory")
    LRDest = .Cells(.Rows.Count, 2).End(xlUp).Row
    SrcRng.Copy .Cells(LRDest + 1, 2)
End With
Dim answer As Integer
answer = MsgBox("Data sent to inventory! Do you wish to clear the sheet?", vbYesNo + vbQuestion, "Empty Sheet")
If answer = vbYes Then
    SrcRng.ClearContents

End If

End If

End Sub

我试图通过这些更改来解决它：

With Sheets("General inventory")
    LRDest = .Cells(.Rows.Count, 2).End(xlUp).Row
    SrcRng.Copy .Cells(LRDest + 1, 2).PasteSpecial(xlPasteValues)
End With
Dim answer As Integer
answer = MsgBox("Data sent to inventory! Do you wish to clear the sheet?", vbYesNo + vbQuestion, "Empty Sheet")
If answer = vbYes Then
    SrcRng.ClearContents

但它给了我错误

运行时错误“1004”：

无法获取 Range 类的粘贴特殊属性

我不知道如何修复它，或者我是否应该使用它PasteSpecial。

感谢您花时间阅读本文，并感谢您提供的任何建议。

干杯，

大卫。-

标签： excelvba

Answer 1

由于您只对粘贴值感兴趣，因此单行将是：

.Cells(LRDest + 1, 2).Resize(SrcRng.Rows.Count, SrcRng.Columns.Count).Value = SrcRng.Value            

并将整个事情缩短一点：

With Sheets("General inventory")
    .Cells(.Rows.Count, 2).End(xlUp).Offset(1).Resize(SrcRng.Rows.Count, SrcRng.Columns.Count).Value = SrcRng.Value
End With