CODEWITHSUNDEEP
excelvba
Nathan Spargo
Nathan Spargo

Reputation: 11

Using VBA to switch between active worksheets only

I'm trying to write a macro that changes between active worksheets only after a prescribed amount of time.

I have several hidden sheets, and currently, this code will cycle me to the last visible sheet, and then pause on that sheet for the same amount of time that it would take to cycle through the hidden sheets as well. So I end up looking at my last visible sheet for several times longer than the other sheets.

This is my code.

Public Sub Switch()
 Dim ws As Worksheet

Do
For Each ws In ThisWorkbook.Worksheets
        ws.Activate
        Application.Wait Now() + TimeValue("00:00:05")
        DoEvents
    Next ws
Loop


End Sub

Any help would be amazing!

Upvotes: 1

Views: 1444

Answers (1)

YowE3K
YowE3K

Reputation: 23984

Use the worksheet's Visible property:

Public Sub Switch()
    Dim ws As Worksheet

    Do
        For Each ws In ThisWorkbook.Worksheets
            If ws.Visible = xlSheetVisible Then
                'Only process things if the sheet is visible
                ws.Activate
                Application.Wait Now() + TimeValue("00:00:05")
                DoEvents
            End If
        Next ws
    Loop
End Sub

Upvotes: 3

Related Questions