Hi,

I have the following macro that is working fine. It is looking at a
specified worksheet array and executing the macro "Filter" on each
worksheets. However, in case one of the worksheet is not existing, I
would like the macro to pass the non existing worksheet and continue
running on the next available worksheet. I tried adding the following
statement: "On Error Resume Next" at the beginning but it is passing
everything.

Any help would be appreciated.
Thanks

Sub UpdateAll()
For Each sh In Worksheets(Array("1", "2", "3", "4", "5", "6", "7",
"10", "11", "12", "21", "22", _
"23", "24", "25", "26", "27", "28", "29", "30", "31", "41", "42",
"43", "44", "45", "46", _
"47", "48", "49", "50", "51", "52", "53", "54", "55", "61"))
sh.Select
Call Filter
Next sh
End Sub

I'd try...

Option Explicit
Sub UpdateAll()
Dim iCtr As Long
Dim myArr As Variant
Dim TestWks As Worksheet

myArr = Array("1", "2", "3", "4", "5", "6", "7", _
"10", "11", "12", "21", "22", _
"23", "24", "25", "26", "27", _
"28", "29", "30", "31", "41", "42", _
"43", "44", "45", "46", _
"47", "48", "49", "50", "51", "52", _
"53", "54", "55", "61")

For iCtr = LBound(myArr) To UBound(myArr)
Set TestWks = Nothing
On Error Resume Next
Set TestWks = Worksheets(myArr(iCtr))
On Error GoTo 0

If TestWks Is Nothing Then
'not found!
Else
TestWks.Select
Call macFilter
End If
Next iCtr

End Sub

I wouldn't use Filter as the name of a procedure. If you search VBA's help,
you'll see that it's used by excel.

Norvascom wrote:

Hi,

I have the following macro that is working fine. It is looking at a
specified worksheet array and executing the macro "Filter" on each
worksheets. However, in case one of the worksheet is not existing, I
would like the macro to pass the non existing worksheet and continue
running on the next available worksheet. I tried adding the following
statement: "On Error Resume Next" at the beginning but it is passing
everything.

Any help would be appreciated.
Thanks

Sub UpdateAll()
For Each sh In Worksheets(Array("1", "2", "3", "4", "5", "6", "7",
"10", "11", "12", "21", "22", _
"23", "24", "25", "26", "27", "28", "29", "30", "31", "41", "42",
"43", "44", "45", "46", _
"47", "48", "49", "50", "51", "52", "53", "54", "55", "61"))
sh.Select
Call Filter
Next sh
End Sub

--

Dave Peterson

Hi,

Not very elegant but it works. Look out for the line wrap on the string of
sheet names


Public sh As Worksheet
Sub UpdateAll()
S =
"1,2,3,4,5,6,7,10,11,12,21,22,23,24,25,26,27,28,29 ,30,31,41,42,43,44,45,46,47,48,49,50,51,52,53,54,5 5,61"
V = Split(S, ",")
For Each sh In ActiveWorkbook.Worksheets
If Not IsError(Application.Match(sh.Name, V, 0)) Then
Call filter
End If
Next
End Sub

Sub filter()
MsgBox "Filter " & sh.Name
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Norvascom" wrote:

Hi,

I have the following macro that is working fine. It is looking at a
specified worksheet array and executing the macro "Filter" on each
worksheets. However, in case one of the worksheet is not existing, I
would like the macro to pass the non existing worksheet and continue
running on the next available worksheet. I tried adding the following
statement: "On Error Resume Next" at the beginning but it is passing
everything.

Any help would be appreciated.
Thanks

Sub UpdateAll()
For Each sh In Worksheets(Array("1", "2", "3", "4", "5", "6", "7",
"10", "11", "12", "21", "22", _
"23", "24", "25", "26", "27", "28", "29", "30", "31", "41", "42",
"43", "44", "45", "46", _
"47", "48", "49", "50", "51", "52", "53", "54", "55", "61"))
sh.Select
Call Filter
Next sh
End Sub
.

I am assuming that the numbers are the actual sheet names and not the sheet
index number. If my assumption is correct then try the code below and there
will be no error.

Using Select Case, even though there is only one case, multiple names can be
used separated by a comma and in effect they become Or operators.

The reason for the error in your code is that if the sheet does not exist,
you cannot use it in the array in the For Each statement.

Sub UpdateAll()
Dim sh As Worksheet
For Each sh In Worksheets

Select Case sh.Name

Case "1", "2", "3", "4", "5", _
"6", "7", "10", "11", "12", _
"21", "22", "23", "24", "25", _
"26", "27", "28", "29", "30", _
"31", "41", "42", "43", "44", _
"45", "46", "47", "48", "49", _
"50", "51", "52", "53", "54", _
"55", "61"

End Select

sh.Select

Call Filter

Next sh
End Sub

--
Regards,

OssieMac

My apologies. An error in the code I posted. It will work better if the
Select and Call are included within the Case instead of outside of it. Also
noted Dave's comment re Filter

Sub UpdateAll()
Dim sh As Worksheet
For Each sh In Worksheets

Select Case sh.Name

Case "1", "2", "3", "4", "5", _
"6", "7", "10", "11", "12", _
"21", "22", "23", "24", "25", _
"26", "27", "28", "29", "30", _
"31", "41", "42", "43", "44", _
"45", "46", "47", "48", "49", _
"50", "51", "52", "53", "54", _
"55", "61"

sh.Select

Call macFilter

End Select


Next sh
End Sub

--
Regards,

OssieMac


"OssieMac" wrote:

I am assuming that the numbers are the actual sheet names and not the sheet
index number. If my assumption is correct then try the code below and there
will be no error.

Using Select Case, even though there is only one case, multiple names can be
used separated by a comma and in effect they become Or operators.

The reason for the error in your code is that if the sheet does not exist,
you cannot use it in the array in the For Each statement.

Sub UpdateAll()
Dim sh As Worksheet
For Each sh In Worksheets

Select Case sh.Name

Case "1", "2", "3", "4", "5", _
"6", "7", "10", "11", "12", _
"21", "22", "23", "24", "25", _
"26", "27", "28", "29", "30", _
"31", "41", "42", "43", "44", _
"45", "46", "47", "48", "49", _
"50", "51", "52", "53", "54", _
"55", "61"

End Select

sh.Select

Call Filter

Next sh
End Sub

--
Regards,

OssieMac

On Feb 28, 5:01*pm, OssieMac
wrote:
My apologies. An error in the code I posted. It will work better if the
Select and Call are included within the Case instead of outside of it. Also
noted Dave's comment re Filter

Sub UpdateAll()
Dim sh As Worksheet
* * For Each sh In Worksheets

* * * Select Case sh.Name

* * * * Case "1", "2", "3", "4", "5", _
* * * * * "6", "7", "10", "11", "12", _
* * * * * "21", "22", "23", "24", "25", _
* * * * * "26", "27", "28", "29", "30", _
* * * * * "31", "41", "42", "43", "44", _
* * * * * "45", "46", "47", "48", "49", _
* * * * * "50", "51", "52", "53", "54", _
* * * * * "55", "61"

* * * * * sh.Select

* * * * * Call macFilter

* * * End Select

* * Next sh
End Sub

--
Regards,

OssieMac



"OssieMac" wrote:
I am assuming that the numbers are the actual sheet names and not the sheet
index number. If my assumption is correct then try the code below and there
will be no error.

Using Select Case, even though there is only one case, multiple names can be
used separated by a comma and in effect they become Or operators.

The reason for the error in your code is that if the sheet does not exist,
you cannot use it in the array in the For Each statement.

Sub UpdateAll()
Dim sh As Worksheet
* * For Each sh In Worksheets

* * * Select Case sh.Name

* * * * Case "1", "2", "3", "4", "5", _
* * * * * "6", "7", "10", "11", "12", _
* * * * * "21", "22", "23", "24", "25", _
* * * * * "26", "27", "28", "29", "30", _
* * * * * "31", "41", "42", "43", "44", _
* * * * * "45", "46", "47", "48", "49", _
* * * * * "50", "51", "52", "53", "54", _
* * * * * "55", "61"

* * * End Select

* * * sh.Select

* * * Call Filter

* * Next sh
End Sub

--
Regards,

OssieMac- Hide quoted text -

- Show quoted text -


Thanks OssieMac

Hi. You would have to add a little more logic to this, but here's an
additional general idea:

Select Case Val(sh.Name)

Case 1 To 7, 10 To 12, 21 To 31 'etc
'Do Something
Case Else
'Do Nothing
End Select

= = = = = = =
HTH :)
Dana DeLouis

On 2/28/2010 8:42 PM, Norvascom wrote:
On Feb 28, 5:01 pm,
wrote:
My apologies. An error in the code I posted. It will work better if the
Select and Call are included within the Case instead of outside of it. Also
noted Dave's comment re Filter

Sub UpdateAll()
Dim sh As Worksheet
For Each sh In Worksheets

Select Case sh.Name

Case "1", "2", "3", "4", "5", _
"6", "7", "10", "11", "12", _
"21", "22", "23", "24", "25", _
"26", "27", "28", "29", "30", _
"31", "41", "42", "43", "44", _
"45", "46", "47", "48", "49", _
"50", "51", "52", "53", "54", _
"55", "61"

sh.Select

Call macFilter

End Select

Next sh
End Sub

--
Regards,

OssieMac



"OssieMac" wrote:
I am assuming that the numbers are the actual sheet names and not the sheet
index number. If my assumption is correct then try the code below and there
will be no error.

Using Select Case, even though there is only one case, multiple names can be
used separated by a comma and in effect they become Or operators.

The reason for the error in your code is that if the sheet does not exist,
you cannot use it in the array in the For Each statement.

Sub UpdateAll()
Dim sh As Worksheet
For Each sh In Worksheets

Select Case sh.Name

Case "1", "2", "3", "4", "5", _
"6", "7", "10", "11", "12", _
"21", "22", "23", "24", "25", _
"26", "27", "28", "29", "30", _
"31", "41", "42", "43", "44", _
"45", "46", "47", "48", "49", _
"50", "51", "52", "53", "54", _
"55", "61"

End Select

sh.Select

Call Filter

Next sh
End Sub

--
Regards,

OssieMac- Hide quoted text -

- Show quoted text -


Thanks OssieMac