仅使用VBA粘贴值:错误“无法获取Range类的PasteSpecial属性 [英] Pasting Values Only using VBA: Error "Unable to get the PasteSpecial property of the Range class

问题描述

我正在尝试使用一个小宏.基本上，我要更改的只是使其仅粘贴值.

I'm trying to get a small macro to work. Basically, all I want to change is making it paste only values.

我并没有编写大部分代码，只是编写了几段代码.它是这样的:

I did not write most of this code, only a few snippets. And it goes like this:

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

我试图解决这些问题，以解决这些问题:

I tried to solve it making these changes:

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':

Runtime error '1004':

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

Unable to get the paste special property of the Range class

我不知道如何解决它，或者我根本不应该使用 PasteSpecial .

I have no idea of how to fix it, or whether I should be using PasteSpecial at all.

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

I appreciate the time you may have taken to read this, and thank you for any advice you may give.

干杯

大卫.-

推荐答案

由于您仅对粘贴值感兴趣，因此一个衬里将是:

since you're interested in pasting values only, a one liner would be:

.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

这篇关于仅使用VBA粘贴值:错误“无法获取Range类的PasteSpecial属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！