Hi
The following bit of code has been working previously excel2003

Dim wks as worksheet
Dim actWkbk As Workbook
Set actWkbk = ActiveWorkbook
With act wkbk
..Worksheets("Sheet1").copy
End with

now in 2007 I am getting Method 'Copy' of object '_Worksheet failed'

What has changed?
Thanks!

On Mon, 19 Nov 2007 12:40:02 -0800, Sandy <(E-Mail Removed)>
wrote:

>Hi
>The following bit of code has been working previously excel2003
>
>Dim wks as worksheet
>Dim actWkbk As Workbook
>Set actWkbk = ActiveWorkbook
>With act wkbk
>.Worksheets("Sheet1").copy
>End with
>
> now in 2007 I am getting Method 'Copy' of object '_Worksheet failed'
>
>What has changed?
>Thanks!

Could it be the <space> between 'act' and 'wkbk' ?

Probably combined with no Option Explicit statement.
--ron

Hi Sandy,

I am using xl2007 with Windows Vista Home and your code works perfectly for
me. (I had to remove the space in 'With act wkbk'; but I assume that is only
a typo when you posted.)

It creates a new workbook with only one worksheet.

--
Regards,

OssieMac


"Sandy" wrote:

> Hi
> The following bit of code has been working previously excel2003
>
> Dim wks as worksheet
> Dim actWkbk As Workbook
> Set actWkbk = ActiveWorkbook
> With act wkbk
> .Worksheets("Sheet1").copy
> End with
>
> now in 2007 I am getting Method 'Copy' of object '_Worksheet failed'
>
> What has changed?
> Thanks!
>

Thanks!
The space was a typo. It appears the problem is the macro is running from
an xls (65000lines X 256 Colimns) and it is copying this sheet into an .xlsm
with a Gazillion columns x 256 katrillion lines.
Thanks again!

"Sandy" wrote:

> Hi
> The following bit of code has been working previously excel2003
>
> Dim wks as worksheet
> Dim actWkbk As Workbook
> Set actWkbk = ActiveWorkbook
> With act wkbk
> .Worksheets("Sheet1").copy
> End with
>
> now in 2007 I am getting Method 'Copy' of object '_Worksheet failed'
>
> What has changed?
> Thanks!
>