I am using Excel 2000

I have a macro which works of the form:

Sheets(Array("Sheet2", "Sheet3")).Select
Sheets(Array("Sheet2", "Sheet3")).Copy

Which creates a new workbook with sheets 1 and 2 in it. The real-life
example has about 20 sheets and it is difficult t o maintain if I add
or re-name sheets.

I want to be able to pick up a list of sheets that I want to copy from
the workbook and put it in place of the "sheet2" etc.

Any ideas ?

I am trying to do it by creating an array and copying the sheets one at
a time, but it seems very cumbersome compared with the starting point
above.

Worksheets.Copy

copies all the worksheets.

If you want to build an array of less than all the sheets, what is the
criteria to determine which sheets to copy.

--

Regards,
Tom Ogilvy


"John Turton" wrote in message
...
I am using Excel 2000

I have a macro which works of the form:

Sheets(Array("Sheet2", "Sheet3")).Select
Sheets(Array("Sheet2", "Sheet3")).Copy

Which creates a new workbook with sheets 1 and 2 in it. The real-life
example has about 20 sheets and it is difficult t o maintain if I add
or re-name sheets.

I want to be able to pick up a list of sheets that I want to copy from
the workbook and put it in place of the "sheet2" etc.

Any ideas ?

I am trying to do it by creating an array and copying the sheets one at
a time, but it seems very cumbersome compared with the starting point
above.