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

Tom Ogilvy


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
ReDim Preserve varr(0 To i - 1)

End Sub

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question