referencing a workbook using string var = error9

[PBcorn]

I'm getting a subs out of range error for the following (section taken from
sub)-

For j = 1 To 4

c = "dataname" & Format(j)
If j = 1 Or j = 2 Then u = v Else u = "TOTAL DATA"
MsgBox c 'first iter gives dataname1 as i would expect
MsgBox u 'first iter gives worksheet name in v as i would expect
MsgBox Workbooks(c).Worksheets(u).Name 'generates error 9
Exit Sub
With Workbooks(c).Worksheets(u) 'generates error 9
.Cells(1, 1).Value = "p"
.Cells(1, 2).Value = "q
.Cells(1, 3).Value = "r"
.Cells(1, 4).Value = "s"
.Cells(1, 5).Value = "t"
.Cells(1, 6).Value = "u"
.Cells(1, 7).Value = "v"
.Cells(1, 8).Value = "w"
.Cells(1, 9).Value = "x"
.Range("a1:i1").Interior.Color = vbRed
End With

Next j

notes:

dataname1 through 4 is dim as string
workbooks called dataname 1 through 4 are all open
v is a string assigned previusly which is the name of the worksheet just
added to the workbooks

c,u, dim as string


msgbox stataments used to debug - c and u variables contain what i would
expect

WHY is this not working!!

Thanks

PB

 

[Muscoby]

The only way I have been able to generate the error in the fashion you
described is that the worksheet as named in variable 'v' does not exist in
the workbook as named in the variable 'c'.

As an additional debugging step:
Put in a For Each Loop to check all the names in the workbook to see if the
worksheet added is exactly as the variable 'v' would have you believe.

(you can delete all of the extra verbage and even the comparison statement
if you want, I just put it in here to try to illustrate what I was
suggesting).

For Each Worksheet In Workbooks(c).Worksheets
If v = Worksheet.Name Then
MsgBox "worksheet name is: " & Worksheet.Name & " new worksheet
added name is: " & v & " - they are the same!"
Else
MsgBox "worksheet name is: " & Worksheet.Name & " new worksheet
added name is: " & v & " - THEY ARE DIFFERENT!"
End If
Next Worksheet