excel - 复制的数据未粘贴到目标工作簿中

Question

我有一个 vba 宏，它尝试对一个工作簿中的数据进行一些简单的格式化，然后将格式化的数据粘贴到另一个工作簿中的表格底部。由于某种原因，我收到运行时错误“1004”:Range 类的 PasterSpecial 方法在粘贴时失败，我不明白为什么。

Sub Add_Data()
'
' Add_Data Macro
'

' Insert column to the left of column B in raw data
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

' Copy columns A-N in raw data
    Range("A2").Select
    Selection.End(xlDown).Select
    Range("A2:N" & ActiveCell.Row).Select
    Selection.Copy

' Remove filter from column B of ongoing report
    Windows("Ongoing Report.xlsm").Activate
    ActiveSheet.ListObjects("OpenJobs_DATA").Range.AutoFilter Field:=2

' Paste data from raw data at bottom of ongoing report
    Range("A2").Select
    Selection.End(xlDown).Select
    Range("A" & ActiveCell.Row + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

' Filter column B of ongoing report to remove blanks
    ActiveSheet.ListObjects("OpenJobs_DATA").Range.AutoFilter Field:=2, _
    Criteria1:="<>"

Answer 1

将范围复制到范围

• 仔细调整常量 (Const) 部分中的值。
• 假设代码将位于包含以下内容的工作簿中RawData 工作表。

提示

• 通常会计算最后或第一个使用的单元格(行、列)从底部或从右侧。如果您的数据低于源范围，我可能是错的。
• 如果可能，最好避免包含 Select 和 Active 的内容。
• 粘贴值时，可以轻松(最好)避免使用复制(粘贴)。

代码

Sub Add_Data()

    Const cSource As String = "RawData"   ' Source Worksheet Name
    Const cCols As String = "A:N"         ' Source Columns Range Address
    Const cFr As Long = 2                 ' Source/Target First Row Number

    Const cWbTarget As String = "Ongoing Report.xlsm"   ' Target Workbook Name
    Const cTarget As String = "Sheet1"    ' Target Worksheet Name
    Const cTgt As String = "A"            ' Target Column Range

    Dim rngS As Range   ' Source Range
    Dim rngT As Range   ' Target Range

    ' In Source Worksheet
    With ThisWorkbook.Worksheets(cSource)
        ' Insert column to the left of column B in raw data.
        .Columns("B:B").Insert Shift:=xlToRight, _
                CopyOrigin:=xlFormatFromLeftOrAbove
        ' In Source Columns Range
        With .Columns(cCols)
            ' Calculate and create a reference to Source First Column Last Used
            ' Cell.
            'Set rngS = .Cells(.Row, .Column).End(xlDown)
            Set rngS = .Cells(.Rows.Count, .Column).End(xlUp)
            ' Calculate and create a reference to Source Range.
            Set rngS = .Rows(cFr).Resize(rngS.Row - cFr + 1)
        End With
    End With

    ' In Target Worksheet
    With Workbooks(cWbTarget).Worksheets(cTarget)
        ' Remove filter from column B of ongoing report
        .ListObjects("OpenJobs_DATA").Range.AutoFilter Field:=2
        ' Calculate and create a reference to Target Column First Empty Cell.
        Set rngT = .Cells(.Rows.Count, cTgt).End(xlUp).Offset(1)
        ' Calculate and create a reference to Target Range i.e. adjust the size
        ' to be equal to the size of Source Range.
        Set rngT = rngT.Resize(rngS.Rows.Count, rngS.Columns.Count)
        ' Copy values from Source Range to Target Range.
        rngT = rngS.Value
        ' Filter column B of ongoing report to remove blanks
        .ListObjects("OpenJobs_DATA").Range.AutoFilter Field:=2, Criteria1:="<>"
    End With

End Sub