can't copy a sheet within same workbook

[Woody]

I'm trying to make a copy of a sheet right after itself. I first tried
this:

Set oSummarySheet = Sheets("WP_DETAIL").Copy After:=Sheets("WP_DETAIL")

I get error:
"Compile error:
Expected: end of statement"

The editor highlights "After", so I try:

Set oSummarySheet = Sheets("WP_DETAIL").Copy(, Sheets("WP_DETAIL"))

Now I get a run-time error '424':
Object Required

The source sheet exists. In the immediate window, I do this:

?Sheets("WP_DETAIL").index
3

I thought it might be confused about what sheets collection, so I tried:

Set oSummarySheet = ThisWorkbook.Sheets("WP_DETAIL").Copy(,
ThisWorkbook.Sheets("WP_DETAIL"))

Got the run-time error 424 again.

What am I doing wrong?

Thanks,
Woody

[dominicb]

Good evening Woody

I think you're making life a bit more difficult for yourself - which is often the way with VBA - but this can be done fairly simply. Try this:

Sheets("WP_DETAIL").Copy After:=Sheets(1)

HTH

DominicB

[Norman Jones]

Hi Woody,

Try:

Sub Tester()
Dim oSumarySheet As Worksheet
Sheets("WP_DETAIL").Copy After:=Sheets("WP_DETAIL")
Set oSummarySheet = ActiveSheet
End Sub

---
Regards,
Norman


"Woody" <[email protected]> wrote in message
news:[email protected]...
> I'm trying to make a copy of a sheet right after itself. I first tried
> this:
>
> Set oSummarySheet = Sheets("WP_DETAIL").Copy After:=Sheets("WP_DETAIL")
>
> I get error:
> "Compile error:
> Expected: end of statement"
>
> The editor highlights "After", so I try:
>
> Set oSummarySheet = Sheets("WP_DETAIL").Copy(, Sheets("WP_DETAIL"))
>
> Now I get a run-time error '424':
> Object Required
>
> The source sheet exists. In the immediate window, I do this:
>
> ?Sheets("WP_DETAIL").index
> 3
>
> I thought it might be confused about what sheets collection, so I tried:
>
> Set oSummarySheet = ThisWorkbook.Sheets("WP_DETAIL").Copy(,
> ThisWorkbook.Sheets("WP_DETAIL"))
>
> Got the run-time error 424 again.
>
> What am I doing wrong?
>
> Thanks,
> Woody

[Woody]

"Norman Jones" <[email protected]> wrote in news:
#[email protected]:

> Sheets("WP_DETAIL").Copy After:=Sheets("WP_DETAIL")
> Set oSummarySheet = ActiveSheet
>

Thanks Norman, that worked perfectlty. Much better than my workaround of
making a blank sheet and copy/paste from source to target.

Woody