Seems like the registry of my sheet names is corrupted

[Douga137]

I have a single workbook application with 3 procedures and 1 function. One procedure calls the other two iteratively
When I try to reference a sheet using a statement like Worksheets("Data").Range("A1")... i get one of two messages
0ne is "subscript out of range" and the other is 'select method of range class failed" I have test code below that I put in the same workbook and executed and got 'select method of range class failed" from the first statement tried to execute.

Sub test()
Worksheets("Data").Range("C5:C215").Select
Worksheets("CompositePDF").Range("C5:C215").Select
Worksheets("FitPDFsht").Range("C5:C215").Select
End Sub

Suggestions are most welcome as I am getting frustrated!

Thanks

 

[Jonmo1]

I even had vba print out a list of my sheet names and there were no blanks there,

Ok, what is the cell that it put this particular sheetname into ?
And given that cell, what does this formula return entered in that sheet
=LEN(A1)

Where A1 is the cell your script put that particular sheet's name in.

 

[Douga137]

jommo1 - strung the names all together and displayed them in a msgbox. I tacked on an 'x' to make sure the last one didn't have a space at the end. They were all clean of any unwanted spaces

 

[shg]

application. goto worksheets("FitPDFsht").ranges(C5:c216") statement caused a 'subscript out of range' message

The syntax should be

Application.Goto Worksheets("FitPDFsht").Range("C5:F216")

If you get a subscript error with that, it means you have misspelled the sheet name.

 

[Jonmo1]

I tacked on an 'x' to make sure the last one didn't have a space at the end.

What about the beginning of the tab's name ? It could be a space as well.

Make a new sheet called SheetList
Run this code

Sub ListSheets()
For Each ws In WorkSheets
    Sheets("SheetList").Range("A1000").End(xlup).Offset(1,0).Value = "x" & ws.Name & "X"
Next ws
End Sub

Now look at the list of names.

 

[Douga137]

Sorry shg, I'm new to foruming and my natural carelessness has got to be curbed - there was no 's' in the actual code Sorry

 

[shg]

All sorted now?

 

[Douga137]

Thanks all - I rebuilt the application from building blocks that still worked and now it works beautifully!

 

[shg]

Good job!