Tim has attended:
Excel VBA Intro Intermediate course
Run-time error '91'
I have created a macro to rename all of the tabs with the name of a coding dimension (Activity) which is referenced in a cell, but when it has finished I get the following message:
"Run time error 91 Object variable or with block variable not set"
The macro works apart from this message. Do you have any suggestions on what to do to stop getting this message?
Thanks
Sub RenameTabsAct()
Dim S As Integer
Dim X As Integer
Sheets("Table of Contents").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet2").Select
X = Sheets.Count
'MsgBox " there are " & (X) & " sheets"
For S = 1 To X
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
ActiveSheet.Next.Select
Next S
End Sub
RE: 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
