potential bug in copy method

[whburling]

My goal is to create a copy of a worksheet and place it within the
same workbook. I chose the copy method as
my approach. furthermore I sought to be a bit more elegant
and hence created a worksheet variable in the same line of code
through the use of the set statement:

Sub test()
Dim aworkbook as workbook
Dim newworksheet as worksheet

with workbooks("aworkbook.xls")
set newworksheet= .worksheets("aworksheet").copy (_
after:=.worksheets("aworksheet"))
newworksheet.name = "test"
end with
end sub

The above code fails upon completing the copy command;
the code DOES copy the worksheet, "aworksheet" and DOES
place it immediately after the original with the name, "aworksheet
(2)" However, the code DOES NOT define the worksheet variable,
newworksheet, with the set method. I get the error message:
'424' Object required.

NOW.....in playing with variations of the above code, I found that
if I replaced the "copy" method with an "add" method, the program at
least executed without error even if I never achieved
my goal of copying. ie:

Sub test()
Dim aworkbook as workbook
Dim newworksheet as worksheet

with workbooks("aworkbook.xls")
set newworksheet= .worksheets.add (_
after:=.worksheets("aworksheet"))
newworksheet.name = "test" 'worked; worksheet name became
"test"
end with
end sub

To me, the fact that the add method did not produce a run-time error
with the "set" command and the copy method did produce a run-time
error suggests there is a bug in the copy method -
that is the copy method does not correctly return an address to the
newly created worksheet object that will become the address associated
with newworksheet.

Does any one else have any insight into why the add method works
without a run-time error but the copy method produces a run-time
error?

 

[Don Guillett]

sub copynamesht()
sheets("aworksheet").copy after:=sheets("aworksheet")
activesheet.name = "test"
end sub

 

[Barb Reinhardt]

I think I'd try this

with workbooks("aworkbook.xls")
.worksheets("aworksheet").copy (_
after:=.worksheets("aworksheet"))
set newworksheet = ActiveSheet
newworksheet.name = "test"
end with
end sub

 

[Peter T]

The worksheet.copy function returns a boolean success, not a worksheet
object. Try something like this -

Sub test2()
Dim wsSource As Worksheet
Dim wsNew As Worksheet

With ActiveWorkbook
Set wsSource = .Worksheets("Sheet1")
wsSource.Copy after:=wsSource
Set wsNew = .Worksheets(wsSource.Index + 1)
wsNew.Name = "NewSheet"
End With
End Sub

However, normally you can do simply
Set wsNew = ActiveSheet
even if the workbook wasn't active originally

Regards,
Peter T