rename during worksheets().copy

izyrider · #1 (**permalink**) 09-05-06, 11:18

hi team!

is there a way to rename the new worksheet at the same time as .Copy After

current code:

Code:

    '   /// copy the "VIDE" worksheet into position
    Worksheets("VIDE").Copy After:=Worksheets(strWS)
    '   /// a not-very-smart way to find the new worksheet
    For Each iws In Worksheets
        If Left$(iws.Name, 4) = "VIDE" Then
            If Len(iws.Name) > 4 Then
                strWS = iws.Name
                Exit For
            End If
        End If
    Next
    Worksheets(strWS).Name = strNom

(yes - i was naïve enough to try searching before asking!)

izy

pootle flump · #2 (**permalink**) 09-05-06, 11:44

Hi Izy

Not to my knowledge or according to the params the copy method accepts (as I'm sure you know).

You could be more efficient getting the sheet:

Code:

Worksheets(Worksheets(strWS).Index +1).Name = strNom

I know you love efficient code:

Code:

LenB(iws.Name) > 8

is ever so slightly more efficient

So - no - short answer is there either isn't an answer or I just don't know it

izyrider · #3 (**permalink**) 09-05-06, 12:01

ta Poots!

i'll go with the first of them: it exactly fits the goal.

this activity only occurs on a user double-click a few times a day so efficiency is not a concern this time. goal was to avoid bumping into 'VIDE (2)', 'VIDE (3)' following a previous error of some sort, in turn triggering an error and leaving 'VIDE (2)', 'VIDE (3)', 'VIDE (4)' ad infinitum. my original attempt failed this test in spades.

izy

Internet Services

Web Development

Digital Marketing

Consumer Tech