Need some code to make all table in worksheets into dynamic table

[Faisal Yunianto]

Hello Guys!

I have 99 worksheet and I want to make all table in sheet to be a dynamic table. When I use recorded macro and I applied to another sheet always had a problem. The notification said “Run-time error ‘1004’: Application-defined or object-defined error”. Can you help me? I want to make all table sheet into dynamic table and the name of dynamic table same as the sheet name and I need some VBA code to handle this. Additional note, I don’t have basic VBA. Thanks!

 

[Georgiboy]

Welcome to the forum Faisal,

Couple of questions:
How many tables will each sheet have?
If there are more than one, what will seperate them?
Do you have sheets that you don't want to make tables from?

Below is a small piece of code that loops through the sheets in a workbook and makes a table from the used range of each sheet, it then names the table after the sheet name:

Sub test()
    Dim ws As Worksheet
    
    For Each ws In Sheets
        ws.ListObjects.Add(xlSrcRange, ws.UsedRange, , xlYes).Name = "tbl" & ws.Name
        ws.ListObjects("tbl" & ws.Name).TableStyle = "TableStyleMedium10" ' optional
    Next ws
End Sub

 

[Faisal Yunianto]

Oh My God, It works! Thank you so much!
Each sheets only have one table

 

[Georgiboy]

You're welcome, thanks for the feedback.