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