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

 

[mikerickson]

The problem is with the way that Select works.
If Data is not the active worksheet, the line

Worksheets("Data").Range("C5:C215").Select

Will error, because one cannot Select a range on any sheet other than the ActiveSheet.

The Application.Goto method doesn't have that fault. Try this.

Sub test2()
    Application.Goto Worksheets("Data").Range("C5:C215")
    Application.Goto Worksheets("CompositePDF").Range("C5:C215")
    Application.Goto Worksheets("FitPDFsht").Range("C5:C215")
End Sub

Alternatly you could Active the sheet before selecting the range.

Worksheets("Data").Activate
Worksheets("Data").Range("C5:C215").Select

 

[JoeMo]

I'm guessing you are trying to run that test from a sheet other than"Data". You can't select a range that's not on the active sheet. So, if you have to select ranges on various sheets (although it's seldom required to do so), select the sheet first and then the range:

Sheets("Data").Select
Range("C5:C215").Select

 

[Douga137]

Joe,

It fixed one error but on another it gave "subscript out of range" on the sheets(FitPDFsht").select statement. any thoughts?

 

[JoeMo]

Joe,

It fixed one error but on another it gave "subscript out of range" on the sheets("FitPDFsht").select statement. any thoughts?

You are missing a left quote mark - is that a typo or is that the way it looks in the macro?

 

[Jonmo1]

You're missing a quote mark here

it gave "subscript out of range" on the sheets("FitPDFsht").select statement. any thoughts?

But I'd guess that's a typo just in the forum, you had it right in the original post.
So

That error is quite simple.
It means the code referenced an object that doesn't exist in the current active book.
Check the exact spelling, and for extra spaces in the actual sheet's TAB name
Maybe it's actually
"FitPDFsht "
or
" FitPDFsht"

 

[Douga137]

just a typo in my reply to your - it's correct in the code

 

[JoeMo]

just a typo in my reply to your - it's correct in the code

Then see jonmo1's response in post #6.

 

[Douga137]

jommo, i did check for extra blanks and found none. I even had vba print out a list of my sheet names and there were no blanks there,

 

[Douga137]

mikerickson - thanks for the suggestion but the application. goto worksheets("FitPDFsht").ranges(C5:c216") statement caused a 'subscript out of range' message