Error '1004' cannot paste table with VBA

[Jeronym]

I am trying to copy table content from table [Table_eudaxrep.driscolls.com_EUDaxViewReport] (let's call this one TableSource) on worksheet [Data] to another table [Table_eudaxrep.driscolls.com_EUDaxViewReport3] (let's call it Table3) on worksheet [Filtered_Data] after I have deleted all content of Table3 and in TableSource all rows with value 0 in column [PFQVALUE].

I use the code below, but run into the following error which I just can't seem to resolve: Run-Time Error '1004': Cannot complete operation: A table cannot overlap with a PivotTable report, query results, a table, merged cells or an XML mapping.

Your help in solving this will be appreciated!

Sub Delete_PFQVALUE_0()
    Dim Last1 As Long
    Dim colPFQVALUE As Integer
   
    Set tbl = ActiveSheet.ListObjects(1)
    With ThisWorkbook.Sheets("Filtered_Data")
        On Error Resume Next
        colPFQVALUE = .Range("Table_eudaxrep.driscolls.com_EUDaxViewReport3[PFQVALUE]").AutoFilter
        Range("Table_eudaxrep.driscolls.com_EUDaxViewReport3[PFQVALUE]").EntireRow.Delete
        On Error GoTo 0
    End With
    With ThisWorkbook.Sheets("Data")
        colPFQVALUE = .Range("Table_eudaxrep.driscolls.com_EUDaxViewReport[PFQVALUE]").Column
        ActiveSheet.ListObjects("Table_eudaxrep.driscolls.com_EUDaxViewReport").Range. _
        AutoFilter Field:=colPFQVALUE, Criteria1:="0"
    If tbl.Range.SpecialCells(xlCellTypeVisible).Areas.Count > 1 Then
            ActiveSheet.ListObjects(1).DataBodyRange.EntireRow.Delete
            ActiveSheet.ListObjects("Table_eudaxrep.driscolls.com_EUDaxViewReport").Range. _
            AutoFilter Field:=colPFQVALUE
            ActiveSheet.AutoFilter.Range.Copy
            Application.Goto Reference:=Worksheets("Filtered_Data").Range("A2"), _
            scroll:=True
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Application.Goto Reference:=Worksheets("Filtered_Data").Range("A2"), _
            scroll:=True
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Exit Sub
    End If
            MsgBox "No PFQVALUE = 0 found, please continue", vbOKOnly + vbExclamation, "Entry Error"
            ActiveSheet.ListObjects("Table_eudaxrep.driscolls.com_EUDaxViewReport").Range. _
            AutoFilter Field:=colPFQVALUE
            ActiveSheet.AutoFilter.Range.Copy
            Application.Goto Reference:=Worksheets("Filtered_Data").Range("A2"), _
            scroll:=True
            ActiveSheet.PasteSpecial Paste:=xlPasteFormats
            ActiveSheet.PasteSpecial Paste:=xlPasteValues
    End With
End Sub

 

[mole999]

sometime pivots are too wide/tall (wider than what you would visually expect), I'm not really sure about what the rules actually are, If I have to automate them i leave maybe 20 columns apart, and just hide the columns not in use

 

[Jeronym]

Ok, so I might be able to solve it by selecting only the columns I need and paste those to the new table? If so I will need to find the VBA code for selecting columns based upon their header row names.

 

[mole999]

not done this part myself, select the columns you want and then give them a range name in the address Name box, so I selected A1&A2 + C1&C2 and in the name box called it MyRange, then select away and in name box type myrange and it selects the cells