I am producing a workbook (DoorShedule) which has macros to rearrange its
named worksheets.

Other XL workbooks may be open at the same time as the DoorSchedule so I
need to minimise all worksheets then maximise/restore those DoorSchedule
sheets as required.

Can you show me how to count the total number of sheets open then could I
use

ActiveWindow.WindowState = xlMinimized

in a loop to minimise all?



Or is there a better way?



Francis Hookham

Why not just activate the DoorSchedule (bring it to the front)

Workbooks("DoorSchedule.xls").Activate


---
HTH
Roger
Shaftesbury (UK)
(Excel 2003, Win XP/SP2)

"Francis Hookham" wrote in message
...
I am producing a workbook (DoorShedule) which has macros to rearrange its
named worksheets.

Other XL workbooks may be open at the same time as the DoorSchedule so I
need to minimise all worksheets then maximise/restore those DoorSchedule
sheets as required.

Can you show me how to count the total number of sheets open then could I
use

ActiveWindow.WindowState = xlMinimized

in a loop to minimise all?



Or is there a better way?



Francis Hookham

numbersheets = worksheets.count

If you have multiple workbooks opened then

numbersheets = 0
for each wkbk in workbooks
numbersheets = numbersheets + workbooks(wkbk.name).worksheets.count
next wkbk

"Roger Whitehead" wrote:

Why not just activate the DoorSchedule (bring it to the front)

Workbooks("DoorSchedule.xls").Activate


---
HTH
Roger
Shaftesbury (UK)
(Excel 2003, Win XP/SP2)

"Francis Hookham" wrote in message
...
I am producing a workbook (DoorShedule) which has macros to rearrange its
named worksheets.

Other XL workbooks may be open at the same time as the DoorSchedule so I
need to minimise all worksheets then maximise/restore those DoorSchedule
sheets as required.

Can you show me how to count the total number of sheets open then could I
use

ActiveWindow.WindowState = xlMinimized

in a loop to minimise all?



Or is there a better way?



Francis Hookham

The point is I need to minimise ALL sheets and then Restore Down only those
I need and then arrange them in various ways. If sheets from other workbooks
are not minimised ther will take part in the arrangement. On the other hand
I do not want to close any other workbook 'open' at the time.

Unless there is a better way I shall work on Joel's lead.

Many thanks Roger

Francis


"Roger Whitehead" wrote in message
...
Why not just activate the DoorSchedule (bring it to the front)

Workbooks("DoorSchedule.xls").Activate


---
HTH
Roger
Shaftesbury (UK)
(Excel 2003, Win XP/SP2)

"Francis Hookham" wrote in message
...
I am producing a workbook (DoorShedule) which has macros to rearrange its
named worksheets.

Other XL workbooks may be open at the same time as the DoorSchedule so I
need to minimise all worksheets then maximise/restore those DoorSchedule
sheets as required.

Can you show me how to count the total number of sheets open then could I
use

ActiveWindow.WindowState = xlMinimized

in a loop to minimise all?



Or is there a better way?



Francis Hookham

Many thanks Joel - I shall work on that - count them and use numbersheets to
minimise them. It was the means of counting I was uncertain of.

Francis



"Joel" wrote in message
...
numbersheets = worksheets.count

If you have multiple workbooks opened then

numbersheets = 0
for each wkbk in workbooks
numbersheets = numbersheets + workbooks(wkbk.name).worksheets.count
next wkbk

"Roger Whitehead" wrote:

Why not just activate the DoorSchedule (bring it to the front)

Workbooks("DoorSchedule.xls").Activate


---
HTH
Roger
Shaftesbury (UK)
(Excel 2003, Win XP/SP2)

"Francis Hookham" wrote in message
...
I am producing a workbook (DoorShedule) which has macros to rearrange
its
named worksheets.

Other XL workbooks may be open at the same time as the DoorSchedule so
I
need to minimise all worksheets then maximise/restore those
DoorSchedule
sheets as required.

Can you show me how to count the total number of sheets open then could
I
use

ActiveWindow.WindowState = xlMinimized

in a loop to minimise all?



Or is there a better way?



Francis Hookham

If you set your DoorSchedule as an object, and refer to it's sheets
explicitly (and other WBs and their Sheets for that matter) you can specify
in your code exactly which Books/Sheets to affect.

Regards
Roger

"Francis Hookham" wrote in message
...
The point is I need to minimise ALL sheets and then Restore Down only
those I need and then arrange them in various ways. If sheets from other
workbooks are not minimised ther will take part in the arrangement. On the
other hand I do not want to close any other workbook 'open' at the time.

Unless there is a better way I shall work on Joel's lead.

Many thanks Roger

Francis


"Roger Whitehead" wrote in message
...
Why not just activate the DoorSchedule (bring it to the front)

Workbooks("DoorSchedule.xls").Activate


---
HTH
Roger
Shaftesbury (UK)
(Excel 2003, Win XP/SP2)

"Francis Hookham" wrote in message
...
I am producing a workbook (DoorShedule) which has macros to rearrange its
named worksheets.

Other XL workbooks may be open at the same time as the DoorSchedule so I
need to minimise all worksheets then maximise/restore those DoorSchedule
sheets as required.

Can you show me how to count the total number of sheets open then could
I use

ActiveWindow.WindowState = xlMinimized

in a loop to minimise all?



Or is there a better way?



Francis Hookham

If you set your DoorSchedule as an object, and refer to it's sheets
explicitly (and other WBs and their Sheets for that matter) you can specify
in your code exactly which Books/Sheets to affect.

Regards
Roger

"Francis Hookham" wrote in message
...
The point is I need to minimise ALL sheets and then Restore Down only
those I need and then arrange them in various ways. If sheets from other
workbooks are not minimised ther will take part in the arrangement. On the
other hand I do not want to close any other workbook 'open' at the time.

Unless there is a better way I shall work on Joel's lead.

Many thanks Roger

Francis


"Roger Whitehead" wrote in message
...
Why not just activate the DoorSchedule (bring it to the front)

Workbooks("DoorSchedule.xls").Activate


---
HTH
Roger
Shaftesbury (UK)
(Excel 2003, Win XP/SP2)

"Francis Hookham" wrote in message
...
I am producing a workbook (DoorShedule) which has macros to rearrange its
named worksheets.

Other XL workbooks may be open at the same time as the DoorSchedule so I
need to minimise all worksheets then maximise/restore those DoorSchedule
sheets as required.

Can you show me how to count the total number of sheets open then could
I use

ActiveWindow.WindowState = xlMinimized

in a loop to minimise all?



Or is there a better way?



Francis Hookham