How many sheets open?

[Francis Hookham]

The Workbook has several pages including "Pages" and Specs"



At any time several could be open and selected and it is necessary to get
back to a particular arrangement. I have not checked but I imagine "Custom
views." are saved in the computer whereas this worksheet will be used on
different computers.



It would be helpful to run a macro which would make sure two specific sheets
were open arranged vertically and one a specific sheet selected. The
following is ok if there was only one sheet open at the start. How do I
reduce the open sheets to only one as the first part of the macro?



Sub SpecsPageOrder()

Sheets("Specs").Select

ActiveWindow.NewWindow

Sheets("Pages").Select

ActiveWindow.Zoom = 75

Windows.Arrange ArrangeStyle:=xlVertical

Windows("Door schedule.xls:1").Activate

ActiveWindow.Zoom = 75

End Sub



Many thanks.



Francis Hookham

 

[NickHK]

Francis,
Worksheets are not "open" in that sense; I believe you want look into the
Windows collection of the workbook.

There is also the CustomViews property of the workbook.

Recording a macro whilst you play with these will give you an idea of the
necessary code:
Window>New Window, Window>Arrange..
View>Custom Views...

I'm exactly sure from your question if want multiple windows or not
though....

NickHK

 

[Francis Hookham]

Sorry Nick - I did not make myself clear - I had developed the macro from a
recording but it depends on there being only one sheet (window) being open
at the start - any sheet (window), but only one.

What I am looking for is a bit of code which will close all but one sheet
(window). Will there have to be an error handler for when there is anyway
only one sheet (window) open?

I realise we are anyway talking about only one workbook being open.

I hope you can help with that.

Francis

 

[Francis Hookham]

Got it - should not have had to bother you (unless there is a less crude
way):

Sub Pages_SPECS_Order()
Application.ScreenUpdating = False
'This will make sure only two windows are open
'arranged vertically - Pages and SPECS, Specs being selected
On Error Resume Next
Windows("Door schedule.xls:3").Activate
On Error Resume Next
Windows("Door schedule.xls:3").Close
On Error Resume Next
Windows("Door schedule.xls:2").Activate
On Error Resume Next
Windows("Door schedule.xls:2").Close
Sheets("Pages").Select
ActiveWindow.NewWindow
Sheets("Pages").Select
ActiveWindow.Zoom = 75
Windows.Arrange ArrangeStyle:=xlVertical
Windows("Door schedule.xls:1").Activate
ActiveWindow.Zoom = 75
End Sub

Francis

 

[NickHK]

Francis,
Avoid the guesswork and use the Windows collection:

Private Sub CommandButton1_Click()
Dim win As Window

With ThisWorkbook
For Each win In .Windows
If .Windows.Count > 1 Then win.Close
Next
End With
End Sub

NickHK

 

[Francis Hookham]

Ah! That's what I wanted - neat, not pedestian like mine.

Many thanks Nick

Francis