问excel vba -错误1004
EN

Stack Overflow用户

提问于 2017-09-21 23:08:26

回答 2查看 352关注 0票数 3

你能帮我解决vba代码中的这个问题吗？(我在论坛上尝试过很多关于1004错误的话题，但我是一个vba新手，无法处理它。)

RaWData工作表中有带有头的表--我需要清理数据部分，然后在下一节中，我将从其他工作表中复制一些数据，其中有数据透视表。

线上的错误(部分的工作表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

谢谢各位，甘卡

vba

excel

回答 2

Stack Overflow用户

发布于 2017-09-21 23:49:09

不确定为什么这会不断地将整个数据集从原始复制到目标。我只需要将过滤过的数据复制到目标中，从目标表SFDCReport中的SFDCReport开始。

   '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

票数 0

Stack Overflow用户

发布于 2017-09-24 23:19:37

更正/解决-由于一些建议，我已经更改了“语法”编码，使之与+一起使用/结束，以便将"address“包含到所有范围对象中。

更改代码的一部分：

'Filter, Select & Copy filtered data to SFDCReport table
        With DS.Range("A1").CurrentRegion
        .AutoFilter Field:=84, Criteria1:=SubRegion
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy SFDCReport.Cells(13, 1)
        End With