Copying worksheets to a new workbook

[John Turton]

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.

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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.