To practise counting through a collection:
- Create a new subroutine called
CountThroughWorksheets and declare a variable which
will be used to refer to the index number of each object in the
Worksheets collection:
Sub CountThroughWorksheets()
Dim WkSheetNum As Integer
End Sub
- Declare a second variable to hold the total number
of worksheets in the workbook and assign the Count
of the Worksheets collection to it:
Sub CountThroughWorksheets()
Dim WkSheetNum As Integer
Dim WkSheetCount As Integer
WkSheetCount = ThisWorkbook.Worksheets.Count
End Sub
- Create a For Next loop which counts from 1
to the count of worksheets:
Sub CountThroughWorksheets()
Dim WkSheetNum As Integer
Dim WkSheetCount As Integer
WkSheetCount = ThisWorkbook.Worksheets.Count
For WkSheetNum = 1 To WkSheetCount
Next WkSheetNum
End Sub
- Declare a variable to hold a reference to a
Worksheet object:
Sub CountThroughWorksheets()
Dim WkSheetNum As Integer
Dim WkSheetCount As Integer
Dim ws As Worksheet
WkSheetCount = ThisWorkbook.Worksheets.Count
For WkSheetNum = 1 To WkSheetCount
Next WkSheetNum
End Sub
- Inside the loop, set the worksheet variable to
reference an object in the Worksheets
collection using the WkSheetNum variable:
Sub CountThroughWorksheets()
Dim WkSheetNum As Integer
Dim WkSheetCount As Integer
Dim ws As Worksheet
WkSheetCount = ThisWorkbook.Worksheets.Count
For WkSheetNum = 1 To WkSheetCount
Set ws = ThisWorkbook.Worksheets(WkSheetNum)
Next WkSheetNum
End Sub
- Add code inside the loop which converts the range
of cells containing data into a table:
For WkSheetNum = 1 To WkSheetCount
Set ws = ThisWorkbook.Worksheets(WkSheetNum)
ws.ListObjects.Add _
SourceType:=xlSrcRange, _
Source:=ws.Range("A1").CurrentRegion
Next WkSheetNum
- Add an If statement to check if
the first three characters of the worksheet name equals
AFC:
For WkSheetNum = 1 To WkSheetCount
Set ws = ThisWorkbook.Worksheets(WkSheetNum)
ws.ListObjects.Add _
SourceType:=xlSrcRange, _
Source:=ws.Range("A1").CurrentRegion
If Left(ws.Name, 3) = "AFC" Then
End If
Next WkSheetNum
- If the condition is met, apply a table style to
the table that you have created. Apply a different style
to the non-AFC worksheets:
For WkSheetNum = 1 To WkSheetCount
Set ws = ThisWorkbook.Worksheets(WkSheetNum)
ws.ListObjects.Add _
SourceType:=xlSrcRange, _
Source:=ws.Range("A1").CurrentRegion
If Left(ws.Name, 3) = "AFC" Then
ws.ListObjects(1).TableStyle = "TableStyleDark2"
Else
ws.ListObjects(1).TableStyle = "TableStyleDark3"
End If
Next WkSheetNum
- Run the subroutine and check the results in Excel:
Each worksheet should have a formatted table.
- You can't run this subroutine again as you can't
create a new table where one already exists. Begin a new
subroutine called ClearEveryTable and declare
four variables as shown below:
Sub ClearEveryTable()
Dim WkSheetNum As Integer
Dim TableNum As Integer
Dim ws As Worksheet
Dim tbl As ListObject
End Sub
- Add a loop to count through the
Worksheets collection and set a reference to a
worksheet object each time:
Sub ClearEveryTable()
Dim WkSheetNum As Integer
Dim TableNum As Integer
Dim ws As Worksheet
Dim tbl As ListObject
For WkSheetNum = 1 To ThisWorkbook.Worksheets.Count
Set ws = ThisWorkbook.Worksheets(WkSheetNum)
Next WkSheetNum
End Sub
- Add a second loop within the first to count
through the collection of ListObjects on the
worksheet and set a reference to a ListObject
item each time:
Sub ClearEveryTable()
Dim WkSheetNum As Integer
Dim TableNum As Integer
Dim ws As Worksheet
Dim tbl As ListObject
For WkSheetNum = 1 To ThisWorkbook.Worksheets.Count
Set ws = ThisWorkbook.Worksheets(WkSheetNum)
For TableNum = 1 To ws.ListObjects.Count
Set tbl = ws.ListObjects(TableNum)
Next TableNum
Next WkSheetNum
End Sub
- Apply the Unlist method to each
item in the ListObjects collection:
Sub ClearEveryTable()
Dim WkSheetNum As Integer
Dim TableNum As Integer
Dim ws As Worksheet
Dim tbl As ListObject
For WkSheetNum = 1 To ThisWorkbook.Worksheets.Count
Set ws = ThisWorkbook.Worksheets(WkSheetNum)
For TableNum = 1 To ws.ListObjects.Count
Set tbl = ws.ListObjects(TableNum)
tbl.Unlist
Next TableNum
Next WkSheetNum
End Sub
- Run the subroutine and check the results in
Excel:
Although the formatting has been retained, the table has been converted into a normal range of cells.
- Save and close the workbook.
