Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

runtime error

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Run-time error '91'

Run-time error '91'

ResolvedVersion 2010

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

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Formst Excel to display leading zeroes

Select the cells that you want to have displaying leading zeroes.

From the 'Format' menu select 'Cells' (Or Right-click on the selected range and and select 'Format Cells').

In the 'Number' tab click on 'Custom' in the 'Category' window.

In the 'Type:' box enter zeroes that correspond to the size of the required number (eg 5 zeroes).

Click OK.

When you enter numbers into these cells, leading zeroes will be displayed.

eg 123 = 00123

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.