Run time error '91' object variable-explain in plain english??

K

KristyBT

I'm trying to run a macro that I did not set-up and the person that did is no
longer with the company.

I keep getting this error. Can someone explain what I need to look for to
find what's causing this error?

Any help will be appreciated.
 
C

Chip Pearson

An object type variable (such as a Range variable) must be Set to
some existing object before it can be used. For example,

Dim Rng As Range

declares that Rng is a variable of type Range, but at this point
it doesn't refer to any specific range. Therefore, if you then
use code like

Rng.Value = 123

you'll get an error 91, because Rng doesn't refer to anything --
it isn't Set to an existing range. Your code needs to set the Rng
variable to an existing range with code like

Set Rng = Range("A1")

Now that Rng refers to an actual range (it has been Set to a
range), you can use it in your code, as in

Rng.Value = 123

In this example, I used a Range type variable, but the same
concepts apply to any Object type variable (e.g., a worksheet, a
workbook, etc).

It would be helpful if you posted the code that is causing the
error.



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
K

KristyBT

Selection.AutoFilter Field:=4, Criteria1:="4/1/2006"
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select

The last line pasted above is where it gets stuck.
 
C

Chip Pearson

The problem arises when the ActiveSheet is the last worksheet in
the workbook. Since it is the last sheet, there is no Next sheet,
so the Next property returns Nothing. Thus you get the error 91.
Change your code to something like


If ActiveSheet.Index < Sheets.Count Then
ActiveSheet.Next.Select
End If
If ActiveSheet.Index < Sheets.Count Then
ActiveSheet.Next.Select
End If
If ActiveSheet.Index < Sheets.Count Then
ActiveSheet.Next.Select
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Top