Workbook name

[Francis Hookham]

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

 

[JE McGimpsey]

One way:

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

 

[Bernie Deitrick]

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]

Many thanks JE McG - just what I wanted

Francis

 

[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

 

[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

 

[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