我有两张表,原始数据和一个用户创建的varSheetName
。
我将一个巨大的数据提取转储到原始数据中,并将列(基于标题字段,我认为这没有问题(提取到新的工作表中。页眉所在的列号总是不同的,它下面的行数也是不同的;型号/0/可用性";,在列?上?,具有要复制到CCD_ 2的行。
知道副本&粘贴列#和最后一行#,我运行以下代码:
With ThisWorkbook.Sheets(varSheetName)
.Range(.Cells(intPasteRow, intPasteCol), .Cells(intLastRow, intPasteCol)).Value = Worksheets("Raw Data").Range(.Cells(1, iCopyCol), .Cells(intLastRow, iCopyCol)).Value
End With
我得到
运行时1004:应用程序定义或对象定义错误
通过分配复制范围
-
以这种方式复制时,需要确保两个范围的行数和列数相等:
intLastRow - IntPasteRow <> intLastRow - 1 ' unless IntPasteRow = 1
-
为了避免那些长而不可读的行,请使用变量。这就是他们的目的。
代码
Option Explicit
Sub CopyData()
' Workbook
Dim wb As Workbook: Set wb = ThisWorkbook
' Source
Dim sws As Worksheet: Set sws = wb.Worksheets("Raw Data")
Dim srg As Range: Set srg = sws.Range(sws.Cells(1, iCopyCol), _
sws.Cells(intLastRow, iCopyCol))
' Destination
Dim dws As Worksheet: Set dws = wb.Worksheets(varSheetName)
Dim dfCell As Range: Set dfCell = dws.Cells(intPasteRow, intPasteCol)
Dim drg As Range: Set drg = dfCell.Resize(srg.Rows.Count)
' Copy.
drg.Value = srg.Value
End Sub