The following macro (Excel VBA Loop Worksheets) will demonstrate how to loop through each worksheet of a given workbook using a couple different types of loops. Further, I will show how to select a range of cells from a table, and then build new worksheets based on unique values within that range. In the following examples, I copy subsets of the table into each new worksheet based on filter criteria.
This is a nice technique when you need to cut larger datasets down into smaller subsets. Also, the code allows you to focus on a specific group or attribute within the main table. I use this often when I want specific groups of users to focus on their own datasets.
The actual VBA code below is quite simple. I encourage you to copy into your own VBA Project. As always, you can modify the code to suit your needs.
Example 1: Excel VBA Loop Worksheets – For Loop
Dim tabs As Integer
Dim t As Integer
tabs = ActiveWorkbook.Worksheets.Count
For t = 1 To tabs
Example 2: Excel VBA Loop Worksheets – For Each Loop
Dim t As Worksheet
For Each t In Worksheets
Example 3: Excel VBA Loop Worksheets – Add New Worksheets based on Filtering Table on Main Sheet
For this example, let’s say you have the following table:
Using the following VBA code:
Dim ws As Worksheet
Set ws = ActiveWorkbook.ActiveSheet
ActiveSheet.Range("A1:A65536").AdvancedFilter Action:=xlFilterCopy, _
Dim rng As Range
Set rng = ActiveSheet.Range(ActiveCell, ActiveCell.End(xlDown))
For Each cell In rng
.AutoFilterMode = False
.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="=" & cell.Value
.AutoFilterMode = False
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Week" & cell.Value
Application.CutCopyMode = False
In short, after running the code above, you now see four new worksheets. Correspondingly, each worksheet is custom named “Week1”, “Week2”, “Week3” and “Week4” based on the unique field values in column A (Week).
To begin with, the code identifies the unique values in column A. Then, these unique values get copied into a separate range. Furthermore, using a For Each Loop, the code loops through each cell in the range. During the loop, a new worksheet is created for each value. The code then copies the content of the main table filtered to the value in the range. Finally, as the worksheets are created, the code copies and pastes the filtered values into each respective worksheet.
Here is the end result:
As a result of running the macro, you can now see the filtered data is copied over:
Table – Highlight cells, then copy and paste special values into your Excel Workbook:
If you would like to post any additional code examples, please add them to the comments below.