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
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