I need to activate one or other windows by switching from one to the other
rather than by name of sheet:

Windows("DoorSchedule.xls:1").Activate
Windows("DoorSchedule.xls:2").Activate

but this does not work if the Workbook is renamed.

Please show me how to write something like:

Windows(Workbook.name + ".xls:1").Activate

Thanks

Francis Hookham

One way:

Windows(Workbook.Name & ":1").Activate



In article ,
"Francis Hookham" wrote:

I need to activate one or other windows by switching from one to the other
rather than by name of sheet:

Windows("DoorSchedule.xls:1").Activate
Windows("DoorSchedule.xls:2").Activate

but this does not work if the Workbook is renamed.

Please show me how to write something like:

Windows(Workbook.name + ".xls:1").Activate

Thanks

Francis Hookham

Francis,

Sub TryNow()
Dim myW As Window

For Each myW In ActiveWorkbook.Windows
Right(myW.Caption, 2) = ":1" Then
myW.Activate
Exit Sub
End If
Next myW

End Sub

You may also want to change ActiveWorkbook to ThisWorkbook, or another Workbook object, like to

Dim myB As Workbook
Set myB = Workbooks.Open(Application.GetOpenFilename)

'Other code, then

For Each myW In myB.Windows


HTH,
Bernie
MS Excel MVP


"Francis Hookham" wrote in message
...
I need to activate one or other windows by switching from one to the other rather than by name of
sheet:

Windows("DoorSchedule.xls:1").Activate
Windows("DoorSchedule.xls:2").Activate

but this does not work if the Workbook is renamed.

Please show me how to write something like:

Windows(Workbook.name + ".xls:1").Activate

Thanks

Francis Hookham

Many thanks JE McG - just what I wanted

Francis


"JE McGimpsey" wrote in message
...
One way:

Windows(Workbook.Name & ":1").Activate



In article ,
"Francis Hookham" wrote:

I need to activate one or other windows by switching from one to the
other
rather than by name of sheet:

Windows("DoorSchedule.xls:1").Activate
Windows("DoorSchedule.xls:2").Activate

but this does not work if the Workbook is renamed.

Please show me how to write something like:

Windows(Workbook.name + ".xls:1").Activate

Thanks

Francis Hookham

Many thanks, Bernie _ I'll use McG's for now - it was what I was trying to
do - but I'll examine yours to see how it works.

Francis


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Francis,

Sub TryNow()
Dim myW As Window

For Each myW In ActiveWorkbook.Windows
Right(myW.Caption, 2) = ":1" Then
myW.Activate
Exit Sub
End If
Next myW

End Sub

You may also want to change ActiveWorkbook to ThisWorkbook, or another
Workbook object, like to

Dim myB As Workbook
Set myB = Workbooks.Open(Application.GetOpenFilename)

'Other code, then

For Each myW In myB.Windows


HTH,
Bernie
MS Excel MVP


"Francis Hookham" wrote in message
...
I need to activate one or other windows by switching from one to the other
rather than by name of sheet:

Windows("DoorSchedule.xls:1").Activate
Windows("DoorSchedule.xls:2").Activate

but this does not work if the Workbook is renamed.

Please show me how to write something like:

Windows(Workbook.name + ".xls:1").Activate

Thanks

Francis Hookham

Oh dear! Replace has not worked - here is one of the subs. (I have included
the original lines of code as comments so you can see what did work). I hope
you can you see what is wrong.

Sub vPages_Specs()
' Display Pages and Schedule sheets vertically
Application.ScreenUpdating = False
'This will make sure only two windows are open arranged vertically
With ThisWorkbook
For Each win In .Windows
If .Windows.Count 1 Then win.Close
Next
End With
Sheets("Specs").Select
ActiveWindow.NewWindow
' Windows("DoorSchedule.xls:1").Activate
Windows(Workbook.Name & ":1").Activate
ActiveWindow.Zoom = 75
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Range("C2").Select
' Windows("DoorSchedule.xls:2").Activate
Windows(Workbook.Name & ":2").Activate
Sheets("Pages").Select
ActiveWindow.Zoom = 75
Windows.Arrange ArrangeStyle:=xlVertical
' scroll to last door
iR = Sheets("Pages").Cells(Rows.Count, 5).End(xlUp).Row
ActiveWindow.ScrollRow = iR
Cells(iR, 3).Select
' Windows("DoorSchedule.xls:1").Activate
Windows(Workbook.Name & ":1").Activate
End Sub



"JE McGimpsey" wrote in message
...
One way:

Windows(Workbook.Name & ":1").Activate



In article ,
"Francis Hookham" wrote:

I need to activate one or other windows by switching from one to the
other
rather than by name of sheet:

Windows("DoorSchedule.xls:1").Activate
Windows("DoorSchedule.xls:2").Activate

but this does not work if the Workbook is renamed.

Please show me how to write something like:

Windows(Workbook.name + ".xls:1").Activate

Thanks

Francis Hookham

Got it at last:

Windows(ActiveWorkbook.Name + ":1").Activate
or
Windows(ActiveWorkbook.Name & ":1").Activate

Intreaging for an amateur that it seems to make no difference using
+ or & (plus or ampersand) !

Francis


"JE McGimpsey" wrote in message
...
One way:

Windows(Workbook.Name & ":1").Activate



In article ,
"Francis Hookham" wrote:

I need to activate one or other windows by switching from one to the
other
rather than by name of sheet:

Windows("DoorSchedule.xls:1").Activate
Windows("DoorSchedule.xls:2").Activate

but this does not work if the Workbook is renamed.

Please show me how to write something like:

Windows(Workbook.name + ".xls:1").Activate

Thanks

Francis Hookham