CODEWITHSUNDEEP
vbaexcelloops
chs
chs

Reputation: 25

Looping through worksheets to convert range into table

I am trying to create a looping procedure that goes through multiple worksheets and converts the data into a table. I think my code keeps breaking because obviously each array can only have one instance of "Table1". I have a couple of questions:

1: is there a way to dynamically name each table. Ex: set the name of the table to the worksheet title?

2: how can i create error handling code that exits the sub routine if there is already an existing table. EX. I have already run this on my existing workbook, but added a new sheet with raw data and still need to convert it to a table.

Here is what I have so far, any and all help is appreciated (new to VBA)

Sub newTables()


' newTables Macro
' T
'
' Keyboard Shortcut: Ctrl+Shift+T

Dim rngSelectionRange As Range
Set rngSelectionRange = ActiveSheet.Range(Selection.Address)

For i = 1 To Worksheets.Count

    Worksheets(i).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    'where it breaks
    ActiveSheet.ListObjects.Add(xlSrcRange, rngSelectionRange, , xlYes).Name = "Table1"
    'Range("Table1[#All]").Select
    'ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
    'ActiveSheet.ListObjects("Employ").Name = "Employee_Data"
    If ActiveSheet.ListObjects.Count > 1 Then
    End If

Next i
End Sub

Upvotes: 1

Views: 3985

Answers (2)

Doug Glancy
Doug Glancy

Reputation: 27488

I think this is basically what you want. You can check if a range has a ListObject, which is handy here:

Sub AddTablesIfNone()
Dim ws As Excel.Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        With ws
            If .UsedRange.ListObject Is Nothing Then
                .ListObjects.Add SourceType:=xlSrcRange, Source:=.UsedRange, xllistobjecthasHeaders:=xlYes
            End If
        End With
    Next ws
    End Sub

Upvotes: 2

Mark Wickett
Mark Wickett

Reputation: 103

How about:

ActiveSheet.ListObjects.Add(xlSrcRange, rngSelectionRange, , xlYes).Name = "Table" & i

This will name the first table in the loop Table1, then Table2, etc.

Or if you want the worksheet's name, replace "Table" & i with: Worksheets(i).Name, though perhaps you want to add a prefix of say, "Table" just to avoid confusion.

Upvotes: 0

Related Questions