Excel VBA - 错误 1004

Question

你能帮我解决我的 vba 代码中的这个问题吗？ (我试图在论坛上浏览许多有关 1004 错误的主题，但我是一个 vba 新手，无法处理它..)。

• RaWData 工作表中有一个带有标题的表格 - 我需要清理数据部分，然后在下一部分中我将从其他工作表(其中是数据透视表)复制一些数据

线路错误(“工作表原始数据清理”部分):

RawData.Range(Cells(2, 1), Cells(LastRow, LastCol)).Delete

不是完整的代码，但这里有一点:

'Exporting
Dim FZ As Workbook
Dim Cesta As Variant
Dim i As Long
Dim SubRegion As String
Dim rTable As Range
Dim CurrDate As String
Dim RawData As Worksheet
Dim SFDCReport As Worksheet
Dim MS As Worksheet
Dim DS As Worksheet
Dim DealOffice As Worksheet

Set DS = ThisWorkbook.Sheets("Data")
Set MS = ThisWorkbook.Sheets("Macro")
Cesta = Application.GetOpenFilename
Set FZ = Workbooks.Open(Filename:=Cesta, Local:=True)
Set RawData = FZ.Sheets("RawData")
Set SFDCReport = FZ.Sheets("SFDC Report")
Set DealOffice = FZ.Sheets("Coverage DealOffice")
CurrDate = MS.Range("E1").Value

For i = 1 To PRFilter
    'Check if Export column is not empty for each SubRegion, if yes, skip to next Subregion(Iteration)
    If IsEmpty(MS.Cells(i + 1, 2).Value) Then
    GoTo NextIteration
        Else 'Things to do if "Not Empty"
        'SubRegion value paste into C10 so Highlights section is updated
        SubRegion = MS.Cells(i + 1, 1).Value
        SFDCReport.Cells(10, 3).Value = SubRegion

    'Sheet SFDC Report Cleaning
    With SFDCReport
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(12, .Columns.Count).End(xlToLeft).Column
    .Range(Cells(14, 1), Cells(LastRow, LastCol)).Delete
    End With

    'Filter, Select & Copy filtered data to SFDCReport table
    DS.Range("A1").CurrentRegion.AutoFilter Field:=84, Criteria1:=SubRegion
    Set rTable = DS.AutoFilter.Range
    Set rTable = rTable.Resize(rTable.Rows.Count - 1)
    Set rTable = rTable.Offset(1) 'Move new range down to start at the first data row
    rTable.Copy
    SFDCReport.Cells(13, 1).PasteSpecial xlPasteValues
    DealOffice.PivotTables("PivotTable1").RefreshTable 'Refresh PivotTable on DealOffice Sheet

    'Sheet RawData Cleaning
    LastCol = RawData.UsedRange.Columns.Count
    LastRow = RawData.UsedRange.Rows.Count
    RawData.Range(Cells(2, 1), Cells(LastRow, LastCol)).Delete

    'Sheet CoverageDealOffice Pivot data copying to RawData
    With DealOffice
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(17, .Columns.Count).End(xlToLeft).Column
    .Range(Cells(17, 1), Cells(LastRow - 1, LastCol)).Copy
    End With
    RawData.Cells(2, 1).PasteSpecial xlPasteValues

    'Formatting/other changes & Saving
    SFDCReport.Activate
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
    ActiveWindow.ScrollColumn = 68
    DealOffice.Select
    FZ.SaveAs Filename:=DirExport & "\" & CurrDate & "_NCE Deal Office Report_" & SubRegion & ".xlsb", FileFormat:=50

NextIteration:
    End If
Next

谢谢大家，Gamca

Answer 1

不知道为什么这会不断将整个数据集从原始数据复制到目标。我只需要将过滤后的数据复制到目标，该数据以目标表 SFDCReport 中的 A13 开头

   'Filter, Select & Copy filtered data to SFDCReport table
    DS.Range("A1").CurrentRegion.AutoFilter Field:=84, Criteria1:=SubRegion
    LastRow = DS.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
    LastCol = DS.AutoFilter.Range.Columns.Count
    Set rTable = DS.AutoFilter.Range
    Set rTable = rTable.Resize(rTable.Rows.Count - 1)
    Set rTable = rTable.Offset(1) 'Move new range down to start at the first data row
    Set rTable2 = SFDCReport.Range(SFDCReport.Cells(13, 1), SFDCReport.Cells(LastRow, LastCol))
    rTable2.Value = rTable.Value