A macro (VBA) to group a number of worksheets

I want to write a macro that will select the pages in an open workbook and then save them to a new workbook.
The following example works provided the sheets have the name 1,2 or 3. My problem is that the sheets do not always have the same name. How can I modify this macro? or is there a better way.

Sub Macro1()
'
Sheets(Array("1", "2", "3")).Select
Sheets("3").Activate
Sheets(Array("1", "2", "3")).Copy Before:=Sheets(1)
End Sub

Thanks
John

On 2002-03-10 18:34, Anonymous wrote:
I want to write a macro that will select the pages in an open workbook and then save them to a new workbook.
The following example works provided the sheets have the name 1,2 or 3. My problem is that the sheets do not always have the same name. How can I modify this macro? or is there a better way.

Sub Macro1()
'
Sheets(Array("1", "2", "3")).Select
Sheets("3").Activate
Sheets(Array("1", "2", "3")).Copy Before:=Sheets(1)
End Sub

Thanks
John

Workbooks("Book1").Worksheets.Copy Before:=Workbooks("Book2").Sheets(1)

Thanks, but this produces a run time 9 error (subscript out of range)

You have to change "Book1" and "Book2" to the names of your workbooks.

[Ivan F Moala]

Try this...

Sub SaveSheets_NewBook()
Dim Sh As Worksheet

For Each Sh In ActiveWorkbook.Sheets
Sh.Select False
Next

Sheets.Copy

Windows(ThisWorkbook.Name).Activate

End Sub


HTH

Ivan

Thanks. Yes I knew I needed to change book1 and book2. More checking shows the problem lies in the book1 end, removing this reference allows the macro to run, but without much control.

In response to Ivans message this produces a runtime error 1004, method select of object_worksheet failed.

Prior to Ivans message I had modified the first response to:
Workbooks.Add Template:= _
"C:Program FilesMicrosoft OfficeTest.xlt"
Windows("test.xls").Activate
Worksheets.Copy Before:=Workbooks("Test1").Sheets(1)
and this seems to work.

[Barrie Davidson]

On 2002-03-10 18:34, Anonymous wrote:
I want to write a macro that will select the pages in an open workbook and then save them to a new workbook.
The following example works provided the sheets have the name 1,2 or 3. My problem is that the sheets do not always have the same name. How can I modify this macro? or is there a better way.

Sub Macro1()
'
Sheets(Array("1", "2", "3")).Select
Sheets("3").Activate
Sheets(Array("1", "2", "3")).Copy Before:=Sheets(1)
End Sub

Thanks
John

Going with Anonymous's code try:

ActiveWorkbook.Worksheets.Copy

This will copy all the worksheets in to a new workbook.

Regards,

I thought all was ok vut the problem is that it is copying the hidden worksheets as well. Can anyone help me set it up so it will not copy the hidden worksheets?

Thanks
John

On 2002-03-11 19:42, Anonymous wrote:
I thought all was ok vut the problem is that it is copying the hidden worksheets as well. Can anyone help me set it up so it will not copy the hidden worksheets?

Thanks
John

Try the following code:

Option Explicit 



Sub SheetArray() 



    Dim sht As Object 

    Dim astrSheets() As Integer 

    Dim intI As Integer 

        

    For Each sht In ActiveWorkbook.Sheets 

        If sht.Visible Then 

            intI = intI + 1 

            ReDim Preserve astrSheets(1 To intI) 

            astrSheets(intI) = sht.Index 

        End If 

    Next sht 

    

    Sheets(astrSheets).Copy Before:=Workbooks("Test1").Sheets(1) 

    

End Sub 

Hope this helps,

Russell

[Russell Hauf]

Sorry, that last post was me.