Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Macro only running Once 2 Questions

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Canberra AU
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi all
    I am having trouble with this macro.
    What I would like to do, is go through all open workbooks and do stuff to every sheet
    this macro works fine Once ie.
    it goes through each sheet in the first workbook then activates the next workbook and goes through each sheet but then
    will not go to the next workbook
    It stops on this line "On Error GoTo NEXTWORKBOOK" with error Object variable or With block variable not set (Error 91)
    I have spent hours trying to get it to work using the help but nothing I do seem to work.

    Sub Next_Sheet_Workbook()
    Do While True
    ' Select Next Sheet
    Do While True
    Application.ActiveSheet.Next.Select
    On Error GoTo NEXTWORKBOOK
    ' do stuff i need to do
    Loop

    NEXTWORKBOOK:
    ' Select next Workbook
    Application.ActiveWindow.ActivateNext
    If ActiveWorkbook.Name = "XXXX.xls" Then
    Exit Sub
    Else
    End If
    Loop
    End Sub

    Is there a way to activate each workbook via an array
    that way only the targeted workbooks are activated, I have an array to open the workbooks, thanks to this forum

    Sub Open_Workbooks()
    Dim ADDRESSBOOKS As Variant
    Dim B As Long

    ADDRESSBOOKS = Array("ACT.xls", "NSW.xls", "QLD.xls", "VIC.xls", "NT.xls", "TAS.xls", "WA.xls", "SA.xls")

    For B = LBound(ADDRESSBOOKS) To UBound(ADDRESSBOOKS)
    Workbooks.Open Filename:=ADDRESSBOOKS(B)
    Next B
    End Sub

    is there a way to use this array to activate the workbooks one at a time and run through each sheet ????

    Appreciated

    Dave

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,

    You can loop through workbooks and sheets using a For Each Next loop because they are both part of a collection e.g.

    Code:
    Sub GoThroughWorkbooksAndSheets()
    Dim wb As Workbook, sht As Worksheet
    
    For Each wb In Application.Workbooks
        For Each sht In wb.Worksheets
            'Do your thing e.g.
            sht.Columns.AutoFit
        Next sht
    Next wb
    End Sub
    Let me know if this isn't what you're after.

    Regards,
    Dan

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Canberra AU
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Dan
    but the "stuff to do" requires the work sheet to be active, and i dont know how to get around that, plus there is one workbook that has all the look up data in it that cant be touched hence the

    If ActiveWorkbook.Name = "XXXX.xls" Then
    Exit Sub
    that why i was thinking of an array.

    ps thank for the above I know i can put it to use elseware

    Appreciated

    Dave

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Dan
    but the "stuff to do" requires the work sheet to be active, and i dont know how to get around that, plus there is one workbook that has all the look up data in it that cant be touched hence the

    If ActiveWorkbook.Name = "XXXX.xls" Then
    Exit Sub
    that why i was thinking of an array.

    ps thank for the above I know i can put it to use elseware

    Appreciated

    Dave
    Hi Dave,

    There's nothing I can think of where you need to activate something before you can work with it. Perhaps if you posted your 'stuff to do' code then I or someone else can help you out.

    Regards,
    Dan

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    Canberra AU
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Dan
    Sorry I have been away for a bit.
    I was going to post it last night but my connection to the web went down.
    I will try again tonight.

    Dave

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
  •