copying excel sheets

[Norah]

If I use the following code to copy a sheet of an excel file :

Sheet,application.worksheets("oldname").copy _
after:=Sheet.application.worksheets("oldname")

the result is O.K.!! : a new sheet (copy of the oldsheet) with the name oldname(2)

But now if either I want to copy oldname(2) to a new sheet or to rename oldname(2) using function renamesheet the result is error "9" (subindex out of the interval). It looks as if the code does not understand a sheet whose name is "oldname(2)". Ir doesn't understand the parenthesis (2). Any help? Thanks

[RobDog888]

I think it may have to do with the way your referring to it. You dont need to use the application object.
A shorter way to reference the sheets is by using the sheets collection. Btw, I tried to simulate it but
it worked fine on my Excel 2003 version. It would make a new copy and name it sheet(x) where x is the
count of sheets in the workbook. Try something like this anyways.

Sheets(1).Copy After:=Sheets("Sheet(4)")
    Sheets(Sheets.Count).Name = "Sheet" & Sheets.Count