New object/automation error

I need to create a new sheet in my macro. So far the code I've been using
looks something like this:

Dim sheet7 as worksheet
sheet7 = new worksheet

But I keep getting error saying that the object doesn't support automation.
I'm still a new programmer and I'm not sure what automation is. I can't seem
to find any documentation to tell me what I'm doing wrong.

Hi Stephen,

Try:

Dim SH As Worksheet

Set SH = Worksheets.Add

---
Regards,
Norman


"Stephen" <[email protected]> wrote in message
news:[email protected]...
>I need to create a new sheet in my macro. So far the code I've been using
> looks something like this:
>
> Dim sheet7 as worksheet
> sheet7 = new worksheet
>
> But I keep getting error saying that the object doesn't support
> automation.
> I'm still a new programmer and I'm not sure what automation is. I can't
> seem
> to find any documentation to tell me what I'm doing wrong.

When objects are created or referenced you need the set statement.

Dim wksNew As Worksheet

Set wksNew = Worksheets.Add

Don't use Sheet7 as your variable as Excel uses Sheet1, Sheet2, ... as it's
default names when new sheets are created and you could run into some
difficulties.
--
HTH...

Jim Thomlinson


"Stephen" wrote:

> I need to create a new sheet in my macro. So far the code I've been using
> looks something like this:
>
> Dim sheet7 as worksheet
> sheet7 = new worksheet
>
> But I keep getting error saying that the object doesn't support automation.
> I'm still a new programmer and I'm not sure what automation is. I can't seem
> to find any documentation to tell me what I'm doing wrong.