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

dougmarkham

Active Member
Joined
Jul 19, 2016
Messages
252
Office Version
  1. 365
Platform
  1. Windows
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?

Code:
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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Does the data on each sheet start in A1?
Also what do you want to call the tables?
 
Upvote 0
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
 
Upvote 0
Ok, how about
Code:
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
 
Upvote 0
Ok, how about
Code:
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.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top