Pause between sheets

[s_smith_iet]

I have a spread sheet that I want to control from a userform. When
the person hits a botton in a userform it opens the spread sheet, goes
to sheet 1 for 2 seconds, then sheet 2 for 2 seconds, sheet 3 for 2
seconds, sheet 4 for 2 seconds and sheet 5 for 2 seconds. I would
like it to loop 3 times and then close the spread sheet.

Please help

 

[Gary Keramidas]

will this work for you?

Option Explicit

Sub display_sheets()
Dim i As Long
Dim ws As Worksheet

For i = 1 To 3
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Application.Wait (Now + TimeValue("0:00:03"))
Next
Next
ActiveWorkbook.Close savechanges:=False
End Sub

 

[s_smith_iet]

Not really
I think it is trying to work but I have two different spread sheets
open and it is trying to cycle through through the wrong one

 

[Gary Keramidas]

be best to supply some workbook names and somebody will help.

 

[Jim Thomlinson]

Change thisworkbook to activeworkbook assuming that the book you want to loop
through is the active book...

For Each ws In Activeworkbook.Worksheets

 

[Gary Keramidas]

try this, just change test.xls to whatever the name of the file you're opening
is. this works if they're in the same folder

Option Explicit

Sub display_sheets()
Dim i As Long
Dim fpath As String
Dim wb1 As Workbook
Dim wb2 As Workbook
fpath = ThisWorkbook.Path
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open(fpath & "\test.xls")
Dim ws As Worksheet

For i = 1 To 3
For Each ws In wb2.Worksheets
ws.Activate
Application.Wait (Now + TimeValue("0:00:03"))
Next
Next
wb2.Close savechanges:=False
End Sub

 

[s_smith_iet]

try this, just change test.xls to whatever the name of the file you're opening
is. this works if they're in the same folder

Option Explicit

Sub display_sheets()
Dim i As Long
Dim fpath As String
Dim wb1 As Workbook
Dim wb2 As Workbook
fpath = ThisWorkbook.Path
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open(fpath & "\test.xls")
Dim ws As Worksheet

For i = 1 To 3
      For Each ws In wb2.Worksheets
            ws.Activate
            Application.Wait (Now + TimeValue("0:00:03"))
      Next
Next
wb2.Close savechanges:=False
End Sub

--

Gary






- Show quoted text -

Got it

Thanks guys!!!!!!!!

 

[Don Guillett]

try this from within the opened workbook

Sub gotosheetspause()
For i = 1 To 5
Sheets(i).Activate
Application.Wait (Now + TimeValue("0:00:02"))
'MsgBox Sheets(i).Name
Next i
End Sub