Excel VBA – Loop Worksheets

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

 

Example 2:  Excel VBA Loop Worksheets – For Each Loop

 

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:

Excel VBA Loop Worksheets

Using the following VBA code:

 

 

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:

Excel VBA Loop Worksheets

As a result of running the macro, you can now see the filtered data is copied over:

Excel VBA Loop Worksheets

 

Table – Highlight cells, then copy and paste special values into your Excel Workbook:

Week Team Opp
1 BUF IND
1 MIA @WAS
1 NE PIT
1 NYJ CLE
2 BUF NE
2 MIA @JAC
2 NE @BUF
2 NYJ @IND
3 BUF @MIA
3 MIA BUF
3 NE JAC
3 NYJ PHI
4 BUF NYG
4 MIA NYJ
4 NE BYE
4 NYJ @MIA

 

If you would like to post any additional code examples, please add them to the comments below.

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

1 thought on “Excel VBA – Loop Worksheets

Leave a Comment