Copying worksheets to a new workbook

  • Thread starter Thread starter John Turton
  • Start date Start date
J

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.
 
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.
 
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.
 
Back
Top