Excel VBA Loops Practice 1 – Nested Loops

Multiple loops can be written within each other to assist in processing multiple series of information. These are called Nested Loops. After the first loop starts, the code then enters another loop. The first loop will not get to process anything further until the second, nested loop has been completed.

In our last tutorial we worked with the For-Each-Next Loop to run down a series of cells to multiple each cell by two. We are now going to add onto now and instead of multiplying the cell by two, we will enter into another loop to add a sheet to our workbook.

The Problem

I have the data in my workbook organized by week number. I want a macro that can copy each week’s data to it’s own sheet. Each sheet is named after the week of the year. If we have a new week of data then that sheet needs to be created.

There is of course the possibility that I open the spreadsheet more than once in a week, so I do not want the macro to create the new sheet if it already exists.

My Cells in column A include a few week numbers. They are:

Application

The below will setup the first loop, going down each cell in column A.
A message box will appear for each cell and tell you it’s value, 49, 50, 51, and 52.

Sub loop_through_ws()

Dim cell As Range, rnge As Range
Set rnge = Range("A2:A5")

For Each cell In rnge

    MsgBox cell.Value
    
Next
End Sub

What we want to do now is to tell the code to check each sheet in our workbook for our current cell value and see if it exists. If it does not, make a new sheet.
Cell value of 49, check sheet numbers.
Cell value of 50, check sheet numbers etc

The below will loop through each cell in my range and then loop through each sheet in my workbook. As is the nature of loops, the sheet loop will not exit until it has processed  through each sheet in your workbook. The order of the loop is:

cell value 49 -> sheet Template, sheet 50, sheet 51, sheet 52
cell value 50 -> sheet Template, sheet 50, sheet 51, sheet 52

Sub loop_through_ws()

Dim cell As range, rnge As range
Dim ws As Worksheet
Set rnge = range("A2:A5")

For Each cell In rnge


    For Each ws In ActiveWorkbook.Worksheets
    
    MsgBox ws.Name
    
    Next
    
Next

End Sub

What we need to do now is add in some conditional logic to check and see if our sheet exists or not. If it does not, add a new sheet and name it for the week number. We also want to make sure the sheet “Template” is being ignored in our loop. Once I process through all of my sheets, I want to store the max week (sheet) number I have in a variable. I am going to reset this variable to 0 for each cell loop so that I always know its value, though this isn’t technically needed here.

Sub loop_through_ws()

Dim cell As Range, rnge As Range
Dim ws As Worksheet
Set rnge = Range("A2:A5")

For Each cell In rnge

Maxweek = 0
    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = "Template" Then GoTo A
    If ws.Name > Maxweek Then Maxweek = CInt(ws.Name)
A:
    Next
If cell.Value > Maxweek Then
    ActiveWorkbook.Sheets.Add
    ActiveSheet.Name = cell.Value
End If
    
Next

End Sub

 

Wrap Up

The idea here is that as we are looping through each cell in our range, we are also checking the name of each sheet. We want to ignore the sheet where our data is (Template). We also want to add in a flag to see if our sheet name is greater than or less than our cell value. Worksheet names are stored as text, so we convert that data format to an integer.
During our worksheet loop, we determine what our highest worksheet number is.
If our cell value is greater than our max worksheet name, then we create a new worksheet with the name of of the cell value, 52.

The entire loop process goes through these values, only creating a sheet when the cell value is greater than the Maxweek (highest sheet name).


If you liked this content and found it helpful, please like the page and leave a comment below. You can also ask questions on the Forum to get personalized help on your issues and questions.

Please also consider making a small donation to help with the upkeep of the site and with the development of future content.

$0.99



Categories: Excel, VBA

Tags: , , , , , ,

Leave a Reply

Show Buttons
Hide Buttons
%d bloggers like this: