can't copy a sheet within same workbook
 

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

can't copy a sheet within same workbook
 

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


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=380253

can't copy a sheet within same workbook
 

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" wrote in message
.119...
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

can't copy a sheet within same workbook
 

"Norman Jones" wrote in news:
:

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