Copying worksheets to a new workbook
 

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.

Copying worksheets to a new workbook
 

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.

Copying worksheets to a new workbook
 

Sub aacopysheets()
Dim varr As Variant
Dim i As Long
Dim sh As Worksheet
ReDim varr(0 To ThisWorkbook.Worksheets.Count - 1)
i = 0
For Each sh In ThisWorkbook.Worksheets
If InStr(1, sh.Range("A1").Value, "report", vbTextCompare) Then
varr(i) = sh.Name
i = i + 1
End If
Next
ReDim Preserve varr(0 To i - 1)
Worksheets(varr).Copy


End Sub

In the above example, any sheet that has the word report contained in the
value of cell A1 is copied.

--
Regards,
Tom Ogilvy

"John Turton" wrote in message
...
What sort of criteria could I use ?

At the moment I am copying a specific set of worksheets containing the
assumptions and results of a model. I don't want to copy the sheets
containing the model itself since the whole model is 13Mb.

I want to have a set of sheets containing the assumptions that just get
copied out of the original workbook and a second set of sheets that get
copied out of the workbook and then copied and pasted as values.

None of this is difficult if all the sheets keep their names and none
are added, but the model is still being developed and I am likely to
create new sheets which I want to add to a list rather than editing a
long list in vba.