I am attempting to loop through multiple tables within a single workbook and clear the data on those tables so that new data can be entered. As the row counts, column counts, names, and number of tables can change I was hoping to create a single macro to loop through the tables and perform .DataBodyRange.Delete
Working Code for 1 table called by sheet and object name:
I also found this code on a forum and I believe the IF portion takes into account a table that is already empty so that no error is thrown.
Sub ResetTable()
With ThisWorkbook.Sheets("SheetName").ListObjects("ListObjectName")
If Not .DataBodyRange Is Nothing Then
.DataBodyRange.Delete
End If
End With
End Sub
My attempt at turning this into a more dynamic loop:
The second set of code is failing on With ws .tbl due to a Compile error: Method or data member not found. So I am looking for any help in either fixing the below code or using an entirely different path.
Sub ResetAllTables()
Dim tbl As ListObject
Dim ws As Worksheet
'Loop through each sheet and table in the workbook
For Each ws In ThisWorkbook.Worksheets
For Each tbl In ws.ListObjects
'Do something to all the tables...
With ws.tbl
If Not .DataBodyRange Is Nothing Then
.DataBodyRange.Delete
End If
End With
tbl.ShowTotals = True
Next tbl
Next sht
End Sub
Thanks in advance!
as suggested by BigBen & SRJ,
changing with ws.tbl
to just with tbl
fixed the issue. also had to fix Next sht
to be Next ws
Thanks for the help!