Looping through worksheets: auto-determine cell range in each ws; create dynamic tables??

[dougmarkham]

Hi Folks,

I have been given a workbook with 200 worksheets: each ws contains some data that wish to manipulate.
Is it possible with VBA to loop though the worksheets; determine the CurrentRegion; and, create a dynamic table on each data range?

Currently I use the below VBA to create a dynamic table from an unknown range. Would anybody be willing to help me adapt this VBA to create tables on every worksheet within a workbook?

Sub DynamicTables()


    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlManual


    Dim sht As Worksheet
    Dim rng As Range
    Dim StartCell As Range
    Dim objTable As ListObject
    
    Worksheets("Sheet1").Activate
    ActiveSheet.Range("1:1").Font.Color = vbWhite
    
    Set sht = Worksheets("Sheet1")
    Set StartCell = Range("A1")
    
[COLOR=#006400]    'Select Range[/COLOR]
    StartCell.CurrentRegion.Select
    Set objTable = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
    objTable.TableStyle = "TableStyleLight8"
    
    With ActiveSheet
        .ListObjects(1).Name = "TableName"
        .Columns.AutoFit
    End With
   
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.Calculation = xlAutomatic


End Sub

Kind regards,

Doug.

 

[Fluff]

Does the data on each sheet start in A1?
Also what do you want to call the tables?

 

[dougmarkham]

Does the data on each sheet start in A1?
Also what do you want to call the tables?

Hi Fluff,

Thanks for replying!

Yes, the data starts in A1.
Is it possible to use pull through the ws Tab name for the table name (which is also Cell A2 on each ws)?

Kind regards,

Doug

 

[Fluff]

Ok, how about

Sub dougmarkham()
   Dim Ws As Worksheet
   
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False
   Application.Calculation = xlManual

   For Each Ws In Worksheets
      With Ws.ListObjects.Add(xlSrcRange, Ws.Range("A1").CurrentRegion, , xlYes)
         .TableStyle = "TableStyleLight8"
         .Name = Ws.Name
      End With
      Ws.Columns.AutoFit
   Next Ws
   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
   Application.Calculation = xlAutomatic
End Sub

 

[dougmarkham]

Ok, how about

Sub dougmarkham()
   Dim Ws As Worksheet
   
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False
   Application.Calculation = xlManual

   For Each Ws In Worksheets
      With Ws.ListObjects.Add(xlSrcRange, Ws.Range("A1").CurrentRegion, , xlYes)
         .TableStyle = "TableStyleLight8"
         .Name = Ws.Name
      End With
      Ws.Columns.AutoFit
   Next Ws
   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
   Application.Calculation = xlAutomatic
End Sub

Hi Fluff,

This worked very efficiently!!
Thanks for your help. I'm going to try some other commands using this For Each Ws in Worksheets / Next Ws loop.
Thanks for showing me this , you're awesome!

Kind regards,

Doug.

 

[Fluff]

You're welcome & thanks for the feedback