excel vba - error 1004

Question

could you help me out with this problem in my vba code ? (I was trying to go through many topics about 1004 error on forums, yet I am a vba novice and was not able to deal with it..).

there is Table in RaWData sheet with headers - I need to clean the data part, then in next section I am going to copy there some of the data from other sheet where is Pivot table

Error on the line (section 'Sheet RawData cleaning):

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

Not whole code but here is a bit:

'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

Thanks guys, Gamca

user_8641324 · Answer

not sure why this keeps copying whole data set from original to the destination.. I need only filtered data to be copied into Destination, that begins with A13 in the destination sheet 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

user_8641324 · Answer

Corrected/Solved - Thanks to some advices, I have changed the "syntax" coding to use With/End With + paid attention to include "address" into all Range objects.

part of changed code:

'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

excel vba - error 1004

2 Answers