Error copying Table but not Range Excel VBA

Question

I have a working script that auto-copies specific cells from a Master Sheet to a secondary Sheet. This script works fine if the Master is set as a range but returns an error when converted to a table.

Script:

Option Explicit

Sub FilterAndCopy()
    Dim rng As Range, sht1 As Worksheet, sht2 As Worksheet

    Set sht1 = Worksheets("SHIFT LOG")
    Set sht2 = Worksheets("FAULTS RAISED")

    sht2.UsedRange.ClearContents

    With Intersect(sht1.Columns("B:BP"), sht1.UsedRange)
        .Cells.EntireColumn.Hidden = False ' unhide columns
        If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
        'within B:BP, column B is the first column
        .AutoFilter field:=1, Criteria1:="Faults Raised"
        'within B:BP, Columns B:C, AC:AE, BP are referenced as .Columns A:B, AB:AD, BO
        .Range("A:B, AB:AD, BO:BO").Copy Destination:=sht2.Cells(4, "B")
        .Parent.AutoFilterMode = False

        'no need to delete what was never there
        'within B:BP, Columns C:AA, AE:BN, BP are referenced as .Columns B:Z, AD:BM
        .Range("B:Z").EntireColumn.Hidden = True ' hide columns
        .Range("AD:BM").EntireColumn.Hidden = True ' hide columns
    End With
End Sub

I have tried changing Range to Table throughout the script (see below). But it returns an error on the following line.

Option Explicit

Sub FilterAndCopy()
    Dim rng As Table, sht1 As Worksheet, sht2 As Worksheet

    Set sht1 = Worksheets("SHIFT LOG")
    Set sht2 = Worksheets("FAULTS RAISED")

    sht2.UsedTable.ClearContents

    With Intersect(sht1.Columns("B:BP"), sht1.UsedTable)
        .Cells.EntireColumn.Hidden = False ' unhide columns
        If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
        'within B:BP, column B is the first column
        .AutoFilter field:=1, Criteria1:="Faults Raised"
        'within B:BP, Columns B:C, AC:AE, BP are referenced as .Columns A:B, AB:AD, BO
        .Table("A:B, AB:AD, BO:BO").Copy Destination:=sht2.Cells(4, "B")
        .Parent.AutoFilterMode = False

        'no need to delete what was never there
        'within B:BP, Columns C:AA, AE:BN, BP are referenced as .Columns B:Z, AD:BM
        .Table("B:Z").EntireColumn.Hidden = True ' hide columns
        .Table("AD:BM").EntireColumn.Hidden = True ' hide columns
    End With
End Sub

.AutoFilter field:=1, Criteria1:="Faults Raised"

The error is: Run-time error '1004': Method 'Autofilter' of object 'Range' failed

A table is a `ListObject`. Can you be more specific on how you tried to modify this code? Maybe a small snippet of it, as well as the error thrown. — BigBen, Nov 02 '18 at 00:47
@BigBen, I just swapped everything labelled Range to Table. The error comes at the FilterandCopy line — Chopin, Nov 02 '18 at 01:10
There is no `Table` object in the object model - use the [`ListObject`](https://learn.microsoft.com/en-us/office/vba/api/excel.listobject) and its properties. — BigBen, Nov 02 '18 at 01:13
You want to use structured references to a table instead of range references for the same columns? — Mark Fitzgerald, Nov 02 '18 at 10:41
Exactly what error do you get? Not sure if it's relevant in this case, but [in my experience](https://stackoverflow.com/questions/46058096/tables-interfere-with-vba-range-variables-depending-on-scope), strange things can happen when named ranges are combined with tables/listobjects. — Egalth, Nov 04 '18 at 23:24
Your code that works is very useful to understand what you're trying to do. However your question is about why your code breaks when you change it to use tables. Please also also show us your code after you've attempted to change it to use tables so we can see where you're going wrong. — Michael, Nov 05 '18 at 03:36
@Michael, every time Range is listed in this code gets swapped to Table. When I change this it returns the listed error — Chopin, Nov 05 '18 at 04:20
As per @BigBen's comment, there is no Table object. You need to use ListObjects to work with tables in VBA. You also need to do far more than simply putting "ListObject" in place of "Range". You need to start using the ListObject instead of trying to determine the intersection range and you need to use ListObject methods and properties, which are different to those of Ranges. — Michael, Nov 05 '18 at 05:09

rohrl77 · Accepted Answer · 2018-11-09T10:37:16.170

There is no such thing as a .UsedTable Range. In order to focus only on the table and the data therein you should use the ListObject and the .DataBodyRange property.

This is the basic idea in getting data from a ListObject.

Sub test()

Debug.Print ActiveSheet.ListObjects(1).DataBodyRange.Address

End Sub

Here is your script changed to include the above:

Sub FilterAndCopy()
    Dim rng As Range, sht1 As Worksheet, sht2 As Worksheet

    Set sht1 = Worksheets("SHIFT LOG")
    Set sht2 = Worksheets("FAULTS RAISED")

    sht2.ListObjects(1).DataBodyRange.ClearContents

    With Intersect(sht1.Columns("B:BP"), sht1.ListObjects(1).DataBodyRange)
        .Cells.EntireColumn.Hidden = False ' unhide columns
        If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
        'within B:BP, column B is the first column
        .AutoFilter field:=1, Criteria1:="Faults Raised"
        'within B:BP, Columns B:C, AC:AE, BP are referenced as .Columns A:B, AB:AD, BO
        Dim rngToCopy As Range
        Set rngToCopy = Intersect(.SpecialCells(xlCellTypeVisible), sht1.Range("A:B, AB:AD, BO:BO"))
        Debug.Print rngToCopy.Address
        rngToCopy.Copy Destination:=sht2.Cells(4, "B")
        .Parent.AutoFilterMode = False

        'no need to delete what was never there
        'within B:BP, Columns C:AA, AE:BN, BP are referenced as .Columns B:Z, AD:BM
        .Range("B:Z").EntireColumn.Hidden = True ' hide columns
        .Range("AD:BM").EntireColumn.Hidden = True ' hide columns
    End With
End Sub

@JPA0888 were you able to adjust your code to make it work based on the example i gave? — rohrl77, Nov 06 '18 at 15:23
Thanks @rohrl77, could you include it in the wider script. I'm having trouble putting it all together. — Chopin, Nov 09 '18 at 02:33
I updated your code. There were a few hitches that did make it somewhat more complicated in activating. Try this answer. The thing that makes your code tricky is that you are copying disconected ranges all in one go. — rohrl77, Nov 09 '18 at 10:38

Error copying Table but not Range Excel VBA

1 Answers1