Macro to Loop Through Sheets Until Right Hand Sheet Is Reached

[evil_moses]

Hi,

I am trying to write a macro to start at the sheet on the left hand side of the workbook and perfrom some actions until the right hand sheet is reached. I just don't know how tell the macro to stop looping once the action has been performed on the right hand sheet, and was wondering if somebody is able to fill in the blanks?

Sub Check_Hidden()
Sheets(2).Select
Do Until HELP NEEDED HERE!!!
Columns("J:J").Select
If Selection.ColumnWidth > 0 Then
Hide_All_Salaries
ActiveWorkbook.Save
Exit Sub
End If
ActiveSheet.Next.Select
Loop
End Sub

Thanks for your help, I really appreciate it.

 

[nbrcrunch]

get a count of the sheets, then subtract 1. As long as the 9th sheet is physically the last sheet in the workbook, it will work.

Here's a section of code I use that does it

Sheets.Add After:=Sheets(Sheets.Count)
MyDataSheets = Sheets().Count - 1
combosheet = Sheets().Count

 

[Richard Schollar]

You would probably be best off using a For Next loop rather than a Do Loop - sheets have an index that give their ordinal position from left (sheet 1) to right (sheet 3 in a 3 sheet workbook). Hence:

For i = 1 to Sheets.Count  'adjust this if you don't want to apply to the last sheet eg by Sheets.Count -1 
  Sheets(i).Name = "Sheet" & i
Next i

The above is an example that loops thru the sheets collection and changes the name of the sheet at each iteration.

 

[evil_moses]

Hey,

Thanks for your help!

All I needed was the sheet count - 1, but having the other two bits of code is very useful as well. I just added a increment to the loop and carried on looping until the sheet count - 1 = the increment.

I am posting my code again in case anybody else ever needs it.

Sub Check_Hidden()
MyDataSheets = Sheets().Count - 1
Actions = 0
Sheets(1).Select
Do Until Actions = MyDataSheets
ActiveSheet.Next.Select
Columns("J:J").Select
If Selection.ColumnWidth > 0 Then
Hide_All_Salaries
ActiveWorkbook.Save
Exit Sub
End If
Actions = Actions + 1
Loop
End Sub

 

[evil_moses]

Hi Richard,

I posted my last reply before I saw your comments. Although using the do...Until worked fine, the suggestion you gave is much cleaner so thanks for your input.

Code now is:

Sub Check_Hidden()
For i = 2 To Sheets.Count
Sheets(i).Select
Columns("J:J").Select
If Selection.ColumnWidth > 0 Then
Hide_All_Salaries
ActiveWorkbook.Save
Exit Sub
End If
Next i
End Sub

 

[Richard Schollar]

Something else you can do is remove the Selects from your code - you almost never need to use Select to work with objects and infact including Select will slow your code down.

Sub Check_Hidden()
For i = 2 To Sheets.Count
  If Sheets(i).Columns("J:J").ColumnWidth > 0 Then
    Hide_All_Salaries
    ActiveWorkbook.Save
    Exit Sub
  End If
Next i
End Sub