Pause between sheets

  • Thread starter Thread starter s_smith_iet
  • Start date Start date
S

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
 
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
 
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
 
Change thisworkbook to activeworkbook assuming that the book you want to loop
through is the active book...

For Each ws In Activeworkbook.Worksheets
 
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
 
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 -
Click to expand...

Got it

Thanks guys!!!!!!!!
 
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
 
Back
Top