Macro only running Once 2 Questions

[Staticman]

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

 

[dk]

Dave,

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

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

 

[Staticman]

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

 

[dk]

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

 

[Staticman]

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