Excel 2010 - Check if Table Already exists

[mmetzinger]

OK, so I have the following code in my project that creates a table for a find/copy/paste operation performed later on

    Range("A1", LastCG).Select
    Range("C6").Activate
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1", LastCG), , xlYes). _
        Name = "Table1"
    Range("Table1[[#Headers],[Enroller Name]]").Select

This code works great but my issue is if the user runs the macro again without closing the original workbook the macro will blow up because it will try and create a table on top of the table that already exits.

So what I need to do is put something like a basic if/then statement to check and see if the table exists but I don't know how to call the table.

Can someone help

Example Result

If object.table1 exists then 

else
    Range("A1", LastCG).Select
    Range("C6").Activate
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1", LastCG), , xlYes). _
        Name = "Table1"
    Range("Table1[[#Headers],[Enroller Name]]").Select
end if

 

[mmetzinger]

OK, so I just found my own answer via another post. Here is what I came up with for code

'Check if Table1 exists and either create it or skip this step to prevent duplicate table error
    Dim ListObj As ListObject
    On Error Resume Next
    Set ListObj = ActiveSheet.ListObjects("Table1")
    On Error GoTo 0
    
    If ListObj Is Nothing Then
            'Establish the table for the find company operation
            Range("A1", LastCG).Select
            Range("C6").Activate
            ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1", LastCG), , xlYes). _
                Name = "Table1"
    Else
        'If the table does exist clear filter from column C 
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=3
    End If

'Step into the table
    Range("Table1[[#Headers],[Enroller Name]]").Select

 

[JoeMo]

As an alternative, here's a UDF that can be used:

Function TableExists(ws As Worksheet, tblNam As String) As Boolean
Dim oTbl As ListObject
For Each oTbl In ws.ListObjects
    If oTbl.Name = tblNam Then
        TableExists = True
        Exit Function
    End If
Next oTbl
TableExists = False
End Function
Sub test()
If TableExists(ActiveSheet, "Table1") Then
    MsgBox "Table1 Exists on sheet " & ActiveSheet.Name
Else
     MsgBox "Table1 Does Not Exist on sheet " & ActiveSheet.Name
End If
End Sub