Minimise worksheets

[Francis Hookham]

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

 

[Roger Whitehead]

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

Workbooks("DoorSchedule.xls").Activate

 

[Joel]

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

 

[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

 

[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

 

[Roger Whitehead]

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

 

[Roger Whitehead]

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