Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: A macro (VBA) to group a number of worksheets

  1. #1
    Guest

    Default

    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

  2. #2
    Guest

    Default

    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)



  3. #3
    Guest

    Default

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

  4. #4
    Guest

    Default

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

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Guest

    Default

    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.

  7. #7
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  8. #8
    Guest

    Default

    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

  9. #9
    Guest

    Default

    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

  10. #10
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, that last post was me.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •