Help with nested loop - looping through specified worksheets and rows

ath

New Member
Joined
Dec 10, 2020
Messages
8
Hello,

I am trying to get some code to work where it loops through specific worksheets specified on worksheet CaseList and for each worksheet, it loops through all the rows starting with B13 until it is empty. When i run the code below, it goes to the first worksheet in the list, loops through the rows but does not advance to the next worksheet. it appears, when screen updating is on, that it loops through all of the rows on the first worksheet the same number of times as the number of worksheets that is listed in Caselist. I am sure it is something pretty simple, but i am new to nested loops. Thanks in advance.

The only reason I am doing this is to activate a private sub to run code that resizes merged cells, which the only thing that doesn't work is advancing to the next sheet in the list.

VBA Code:
Dim x As Integer
Dim Sht As Worksheet
Dim sheet_names As Variant
Set sheet_names = ThisWorkbook.Sheets("CaseList").Range("C2:C2000")

For Each Sht In ThisWorkbook.Sheets

If Not IsError(Application.Match(Sht.Name, sheet_names, 0)) Then

Range("B13").Select

Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(-1, 0))

ActiveCell.Value = ActiveCell.Value

ActiveCell.Offset(1, 0).Select

Loop

End If

Next Sht
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
There does not appear to be anything wrong with the code. (although I liked my version better). But what you have to remember is that the computer sees every keystroke as a character, including those that we cannot see. So your problem is probably that the
VBA Code:
If Not IsError(Application.Match(Sht.Name, sheet_names, 0)) Then
statement is not finding sheets because they have leading or trailing spaces in either the sheet name tabs or in the list of names. But I am pretty sure that the For Each Sht loop is working properly to go through the host workbook sheets. You can check that out by opening the vb editor (Alt + F11), left click once anywhere in the body of the code, then use the F8 key to step through the code line by line. After you complete the first sheet and the yellow highlight move back to the 'If Not...' statement, then hover the mouse pointer over the Sht.Name and see if it changed. I am sure it will have done so. If it then does not detect a match from the list but you know there is one there, then look of the non printing characters in the name tabs and cells.
 

ath

New Member
Joined
Dec 10, 2020
Messages
8
I tried what you recommended and it is changing sheet name, but when it would still perform the Do Until loop on the first worksheet in the list. So i tried to change

VBA Code:
Range("B13").Select
to
VBA Code:
Sht.Range("B13").Select
and got an error. Then I added Sht.Select like this:

VBA Code:
Dim x As Integer
Dim Sht As Worksheet
Dim sheet_names As Variant
Set sheet_names = ThisWorkbook.Sheets("CaseList").Range("C2:C2000")

For Each Sht In ThisWorkbook.Sheets

If Not IsError(Application.Match(Sht.Name, sheet_names, 0)) Then

Sht.Select

Range("B13").Select

Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(-1, 0))

ActiveCell.Value = ActiveCell.Value

ActiveCell.Offset(1, 0).Select

Loop

End If

Next Sht

I am not a fan of using select, but it works. Since this will only be used one time when the user runs the code and never again, I will live with it. Thanks for the guidance. it is always much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,983
Messages
5,639,380
Members
417,084
Latest member
elitepaper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top