Indexing Worksheets for a set viewing time

[Eire75]

Hi,

I would like to write some code which will allow me to display each worksheet (9 in total) in a workbook for a set time (perhaps 10 minutes per worksheet) and when finished, iterate the process again indefinitely. I have some code which refreshes the data in each worksheet already and so would like to incorporate both sets of code into a single macro.

The end result will be 9 sets of continuously updated data which will be displayed on a large screen, to be used for process monitoring.

Any help would be greatly appreciated.

Eire.

 

[Ruddles]

Something along these lines:-

Sub Carousel()
 
  Dim ws As Worksheet
  
  Do
    For Each ws In ThisWorkbook.Worksheets
      ws.Activate
      Application.Wait Now() + TimeValue("00:00:03")
      DoEvents
    Next ws
  Loop

End Sub

I've set the pause to three seconds for testing purposes but you'd change the value to whatever you wanted.

 

[Eire75]

Hi Ruddles,

Thanks for this and post the slution. Much appreciated,

Eire75.

 

[Eire75]

Hi Ruddles,

Your code works perfectly, thanks very much for the help.

One other thing - is it possible to index through certain worksheets only? I have ten worksheets, two of which will be hidden. When I run the macro, I would like to wait the 3 seconds (or probably 10 seconds) on the remaining 8 worksheets only. As it is, it still waits for each duration, even on the hidden worksheets. Not a big problem but it would be tidier if I could achieve this.

Thanks again,

Eire75

 

[Ruddles]

You need to test the .Visible property of each sheet and only display and pause for sheets which aren't hidden:-

Sub Carousel()
 
  Dim ws As Worksheet
 
  Do
    For Each ws In ThisWorkbook.Worksheets
      If ws.Visible = True Then
        ws.Activate
        Application.Wait Now() + TimeValue("00:00:03")
      End If
      DoEvents
    Next ws
  Loop
 
End Sub

You could also extend the test to include or exclude sheets depending on other properties such as their names:-

      If ws.Visible = True And ws.Name <> "DoNotShow" Then

That would show all unhidden sheets except the sheet named DoNotShow.