Run-time error '91'

Hello Tim,

Hope you enjoyed your Microsoft Excel VBA course with Best STL.

Thank you for your question regarding the Run-time error '91' you are receiving.

The code you have given us is looking for the next sheet after completing the final sheet naming exercise. As there is no next sheet you will get this error. Unfortunately, the error message is not that helpful in determining what is going wrong. Try a revised version of your code as follows:


Sub RenameTabsAct()

Dim S As Integer
Dim X As Integer

Sheets("Table of Contents").Select

X = Sheets.Count - 1

MsgBox " there are " & (X) & " sheets"

For S = 1 To X

ActiveSheet.Next.Select

Cells.Find(What:="Activity", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Copy

Range("P1").Select
ActiveSheet.Paste

Range("Q1").Value = "=Mid(P1,12,25)"
NewName = Range("Q1").Value
ActiveSheet.Name = NewName
Range("P1:Q1").ClearContents

Next S

End Sub

Don't hide the Table of Contents sheet at the start of the code. If you really wish to do this select that sheet at the end and hide it.


I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer