Loop through worksheets and create tables

Question

I have 10 worksheets. I want to create a table for each. every table has a different amount of data, ive been using the following code for each, but i was wondering how to do it with a loop. I would truly apreciate some help :) Sub table() Dim sht As Worksheet Dim lastrow As Long Dim LastColumn As Long Dim StartCell As Range Set sht = Worksheets("m9") Set StartCell = Range("A1") lastrow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column sht.Range(StartCell, sht.Cells(lastrow, LastColumn)).Select Dim objTable As ListObject Set objTable = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes) End Sub Tried the following, but with no luck Sub loop_test() Dim i As Integer Dim ws_num As Integer Dim starting_ws As Worksheet Set starting_ws = ActiveSheet ws_num = ThisWorkbook.Worksheets.Count For i = 1 To ws_num ThisWorkbook.Worksheets(i).Activate ' Dim lastrow As Long Dim LastColumn As Long Dim StartCell As Range Set StartCell = Range("A1") lastrow = Cells(Rows.Count, StartCell.Column).End(xlUp).Row Range(StartCell, Cells(lastrow, LastColumn)).Select Dim objTable As ListObject Set objTable = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes) Next starting_ws.Activate End Sub JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

You should avoid Activate and Select statements. The following will loop through all worksheets in the workbook and add a ListObject to each sheet. It will also test to see whether there is an already existing ListObject. If the existing ListObject overlaps with the range that you're going to add the table into to, it will convert it to a range before recreating the ListObject Sub loop_test() Dim ws As Worksheet Dim StartCell As Range, TblRng As Range Dim LastRow As Long, LastColumn As Long Dim objTable As ListObject For Each ws In ThisWorkbook.Sheets Set objTable = Nothing With ws Set StartCell = .Range("A1") LastRow = .Cells(.Rows.Count, StartCell.Column).End(xlUp).Row LastColumn = .Cells(StartCell.Row, .Columns.Count).End(xlToLeft).Column Set TblRng = .Range(StartCell, .Cells(LastRow, LastColumn)) ' Test if table exists on sheet On Error Resume Next Set objTable = .ListObjects(1) On Error GoTo 0 ' If table overlaps with TblRng - Convert to Range If Not Intersect(objTable.Range, TblRng) Is Nothing Then objTable.Unlist End If ' Create Table Set objTable = .ListObjects.Add(xlSrcRange, TblRng, , xlYes) End With Next ws End Sub