I showed what was in my Range("O8") and that it worked for me.

I don't know what else to tell you. That is about the only way to do it if
you have this string in a single cell.

sStr = """CAT0"", ""00"", ""01"", ""07"", ""08"""
prange = Array(sStr)

does not work

sStr = """CAT0"", ""00"", ""01"", ""07"", ""08"""
prange = Evaluate( "{" & sStr & "}")

Here, run this code:

Sub Tester4()
Dim sStr As String
Dim prange As Variant
Dim i As Long
sStr = """CAT0"", ""00"", ""01"", ""07"", ""08"""
prange = Evaluate("{" & sStr & "}")
Debug.Print sStr
For i = LBound(prange) To UBound(prange)
Debug.Print i, prange(i), Worksheets(prange(i)).Name
Next
End Sub

this produced:
"CAT0", "00", "01", "07", "08"
1 CAT0 CAT0
2 00 00
3 01 01
4 07 07
5 08 08

If you don't know how to use the immediate window:

Sub Tester4()
Dim sStr As String
Dim prange As Variant
Dim i As Long
sStr = """CAT0"", ""00"", ""01"", ""07"", ""08"""
prange = Evaluate("{" & sStr & "}")
MsgBox sStr
For i = LBound(prange) To UBound(prange)
msgbox i & " " & prange(i) & " " & Worksheets(prange(i)).Name
Next
End Sub

You don't have to use ""00"" in the cell - that is just for building a
string directly.
Regards,
Tom Ogilvy


Gary Burke wrote in message
...

Hi Tom,

Many thanks for your patience, however, same error is still being
returned. (I had tried including the sheet name before responding last
time).

I have also tried with data as follows:-

a) "DEPT TTL", "CAT0", "10", "11", "13", "16"

b) DEPT TTL", "CAT0", "10", "11", "13", "16 - note the missing starting
and ending "

Any other thoughts ?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!