Count Pivot Tables and add a table

manster_mg · May 12th 2022

Hello forum,

I have a weekly Excel workbook with a separate Sheet for each workday. Each Sheet has data that I want to convert into a table with VBA. I recorded the code below but it will not function correctly after the first sheet is saved. The error message comes back with "a table can't overlap another table". Is there a way to count the number of current tables in the workbook and have VBA create a new table with the next Table#?

Code

Sub TableMac()
'
' TableMac Macro
'

    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$AA" & LastRow), , xlYes).Name _
        = "Table1"
    Range("Table1[#All]").Select
    ActiveSheet.ListObjects("Table3").TableStyle = "TableStyleMedium6"
    Range("Table1[[#Headers],[Vndr Num]]").Select
End Sub

Display More

I appreciate your feedback,

Thank you

**royUK** · May 13th 2022

That is just making work. Then create reports from the data

manster_mg · May 18th 2022

Sorry, I don't understand your reply. Was my question confusing? I want to create a table in excel using a macro but if I already have a table in the workbook the macro I recorded does not work.

**royUK** · May 19th 2022

It's poor workbook design to have data separated on different sheets.

Your code will only work on the ActiveSheet and on the specified range, if a Table already exists there you will get that error message.

You need to loop through the sheets check if a Table exists and create the Table if not.

Code

Sub CreateTables()
Dim oTbl As ListObject
Dim oWs As Worksheet

''///Loop through each sheet and table in the workbook
  For Each oWs In ThisWorkbook.Worksheets
''///check if A1 is part of a Table
      If Not TypeName(oWs.Cells(1, 1).Parent) = "Listobject" Then
      ''///if not convert to Table and name using sheet index
    oWs.ListObjects.Add(xlSrcRange, oWs.Cells(1, 1).CurrentRegion, , xlYes).Name _
        = "Table" & oWs.Index
    oWs.ListObjects("Table" & oWs.Index).TableStyle = "TableStyleMedium6"
    End If
  Next oWs

End Sub

Display More

Count Pivot Tables and add a table

Participate now!

Share

Similar Threads

Pivot tables not updating after adding a slicer

Split pivot into multiple charts

Power Pivot - Countif

Pivot table - Shows 'Count of' instead of desired 'Sum of'

Automatically create 10 pivot tables

filter data based on selection dropdown and split into tables of customers

Tags