Cicle through worksheets

[FERCJGP]

Hello everyone, I'm trying whithout success to loop through worksheets hope you can help me

I have a workbook with 40 worksheets of my employes

The 40 worksheets of employes have those names
10 worksheets are for the first group : 22-01, 22-02 ... 22-10
10 worksheets are for the second group : 23-01, 23-02 ... 23-10
10 worksheets are for the third group : 24-01, 24-02 ... 24-10
10 worksheets are for the fourth group : 25-01, 25-02 ... 25-10

On every worksheet that has an active employe, the cell P4 has the same code of the worksheet, and if it has no employe it has a "XX-XX" (means there is no employe on this worksheet).

I want a macro that activate the next worksheet if cell P4 is different of "XX-XX", if not go to next worksheet until cell P4 is different of "XX-XX" and so on.... and lastly, if the macro runs and it reach the end (the worksheet 25-10) then start it over again with the 22-01 worksheet.

This is because today I have 18 employes, and it varies on the year, and everytime I need to add something I loop it manually (CTRL+PAGE UP or DOWN) till I find the next employe, so I want to place two buttons on every worksheet and when I click one it activate the next used worksheet and the other go to the previous used worksheet.

I all ready try the do until loop but it does not work, well I mean I can't make it work

 

[alansidman]

Option Explicit


Sub FindEE()
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Range("P4") = ws.Name Then
            ws.Activate
            Exit Sub
        End If
    Next ws


End Sub

 

[DanteAmor]

I annex 2 codes, one for the previous sheet and the other for the next sheet.

Sub [B][COLOR=#0000ff]Prev_Sheet[/COLOR][/B]()
  Dim actual As Long, wNext As Long, n As Long
  n = 0
  actual = ActiveSheet.Index
  wNext = actual
  If wNext = 1 Then wNext = Sheets.Count + 1
  Do While True
    wNext = wNext - 1
    If Sheets(wNext).Range("P4").Value = "XX-XX" Then
      If wNext = 1 Then wNext = Sheets.Count + 1
    Else
      Exit Do
    End If
    n = n + 1
    If n > Sheets.Count Then
      MsgBox "All sheets have 'XX-XX'"
      Exit Sub
    End If
  Loop
  Sheets(wNext).Select
End Sub


Sub [B][COLOR=#0000ff]Next_Sheet[/COLOR][/B]()
  Dim actual As Long, wNext As Long, n As Long
  n = 0
  actual = ActiveSheet.Index
  wNext = actual + 1
  If wNext > Sheets.Count Then wNext = 1
  Do While True
    If Sheets(wNext).Range("P4").Value = "XX-XX" Then
      If wNext = Sheets.Count Then wNext = 0
    Else
      Exit Do
    End If
    wNext = wNext + 1
    n = n + 1
    If n > Sheets.Count Then
      MsgBox "All sheets have 'XX-XX'"
      Exit Sub
    End If
  Loop
  Sheets(wNext).Select
End Sub

 

[FERCJGP]

it doesn't it goes to the second worksheet no matter what worksheet is selected

 

[FERCJGP]

DanteAmor IT WORKS!!! Thank you so much

 

[FERCJGP]

Sorry I'm new at the forum so I have no idea how to reply...

This reply is for alansidman

it doesn't it goes to the second worksheet no matter what worksheet is selected

 

[alansidman]

Since Dante has supplied a workable solution, there is no need to investigate why. However, it did work in my example I created to test. Good luck as you have a workable solution and that is what this forum is all about.

 

[DanteAmor]

DanteAmor IT WORKS!!! Thank you so much

I'm glad to help you. Thanks for the feedback.