VBA PasteSpecial 转置给出应用程序定义或对象定义的错误 (1004)

Question

我正在尝试从 CSV 文件中复制整列(减去标题(并将其转置粘贴到我的 excel 文件中。

"运行时错误'1004'：应用程序定义或对象定义的错误 (1004("在我尝试运行粘贴转置操作的最后一行标记，我不知道为什么？

代码如下：

Sub UpdateData()
Dim LastRow_1 As Long      ' Set up variable                                                                           
LastRow_1 = Workbooks("ProconData").Worksheets(1).Cells(Rows.Count,1).End(xlUp).Row ' Find last non-blank row

Dim CopyRange_1 As Range      ' Set up variable  
Set CopyRange_1 = Workbooks("ProconData").Worksheets("ProconData").Range("A2").Resize(LastRow_1, 1)  ' Define copy range
Dim PasteRange_1 As Range      ' Set up variable  
Set PasteRange_1 = Workbooks("TBM Ground Condition 
Record").Worksheets("RECORD").Range("LV4") '.Resize(4, 334 + LastRow_1) ' Define paste range
Workbooks("TBM Ground Condition Record").Worksheets("RECORD").Range(PasteRange_1).PasteSpecial Transpose:=True, Paste:=xlPasteValues  ' Transpose & paste data
End Sub

任何帮助将不胜感激！

Answer 1

只需替换：

Workbooks("TBM Ground Condition Record").Worksheets("RECORD").Range(PasteRange_1).PasteSpecial Transpose:=True, Paste:=xlPasteValues  ' Transpose & paste data

跟：

CopyRange_1.Copy ' copy the range to be copied
PasteRange_1.PasteSpecial Transpose:=True, Paste:=xlPasteValues  ' Transpose & paste data ' paste the copied range to the one set for pasting

因为：

1. 您没有复制任何内容
2. 您已将PasteRange_1设置为Range对象，因此可以直接将其用作Range引用。虽然Range对象不能是Range对象本身的Range()属性的参数......(你可以看看文档(

顺便说一句，您的代码假设"ProconData"工作簿的第一个工作表被命名为"ProconData"，这可能并不总是正确的

您最好通过名称引用通缉表，因此进行更改：

LastRow_1 = Workbooks("ProconData").Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row '

自：

LastRow_1 = Workbooks("ProconData").Worksheets("ProconData").Cells(Rows.Count, 1).End(xlUp).Row '

Answer 2

试试下面

Sub UpdateData()
    Dim LastRow_1 As Long      ' Set up variable
    LastRow_1 = Workbooks("ProconData").Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row ' Find last non-blank row
    Dim CopyRange_1 As Range      ' Set up variable
    Set CopyRange_1 = Workbooks("ProconData").Worksheets("ProconData").Range("A2").Resize(LastRow_1, 1)  ' Define copy range
    CopyRange_1.Copy
    Workbooks("TBM Ground Condition Record").Worksheets("RECORD").Range("LV4").PasteSpecial Transpose:=True  '.Resize(4, 334 + LastRow_1) ' Define paste range
End Sub