Copy and rename multiple sheets

I have this code that copies the active worksheet 2x. The original worksheet name is L-2019. I have userform combo box (cboYear ) populated by the year. I would like to rename the 2 new sheets as L-#### and V-#### respectively (#### = cboYear). How can I accomplish this?

Code:

---

Dim wks as worksheet

For x = 1 To 2    ActiveSheet.Copy After:=Sheets("Security")
Next

wks.name = cboYear

---

here is my failed attempt...

Code:

---

        For x = 1 To 2
            ActiveSheet.Copy After:=Sheets("Security")
            If Right(ActiveSheet.name, 3) = "(2)" Then ActiveSheet.name = "L-" & cboYear
            If Right(ActiveSheet.name, 3) = "(3)" Then ActiveSheet.name = "V-" & cboYear
        Next

---

Thank you kindly

Code:

---

    Dim myYear As String
   
    myYear = cboYear.Value
    If Evaluate("isref('L-" & myYear & "'!a1)") Then Exit Sub

    With Sheets("temlate")
        .Copy After:=Sheets("Security")
        ActiveSheet.Name = "L-" & myYear
        .Copy After:=ActiveSheet
        ActiveSheet.Name = "V-" & myYear
    End With

---

When you copy an ActiveSheet, the copy becomes the ActiveSheet.
Try this

Code:

---

Dim wks as worksheet
Set wks = ActiveSheet

With wks
    .Copy After:=Sheets("Security")
    ActiveSheet.name = "L-" & cboYear

    .Copy After:=Sheets("L-" & cboYear)
    ActiveSheet.name = "V-" & cboYear

    .Activate
End With

---

Sam...I merged some coding into your coding and I'm trying to eliminate the redundancy.

Currently...

Code:

---

        With wks
            .Copy After:=Sheets("Security")
            With ActiveSheet
                .name = "L-" & cboYear
               
                ....... (More code)

                .Range("O3") = cboYear & " YTD ROI (%):"
                .Range("P1:P3") = "$0.00"
            End With
           
            .Copy After:=Sheets("L-" & cboYear)
            With ActiveSheet
                .name = "V-" & cboYear
               
                ....... (More Code)

                .Range("O3") = cboYear & " YTD ROI (%):"
                .Range("P1:P3") = "$0.00"
            End With
        End With

---

I've tried this way, but the code renames first created sheet from L-#### into V-####. The second sheet created is name L-#### (2)

Code:

---

        With wks
            .Copy After:=Sheets("Security")
            With ActiveSheet
                .name = "L-" & cboYear
               
                ....... (More code)

                .Range("O3") = cboYear & " YTD ROI (%):"
                .Range("P1:P3") = "$0.00"
            End With 
         
            With ActiveSheet
                .Copy After:=Sheets("Security")
                .name = "V-" & cboYear
            End With
        End With

---

Quote:

---

Code:

---

            With ActiveSheet
                .Copy After:=Sheets("Security")
                .name = "V-" & cboYear
            End With

---

---

Expanding that, one gets
Sheets("L-1999").Copy After:=Sheets("Security")
Sheets("L-1999"). Name = "V-1999"
And you're still left with a sheet named ""L-1999" (2)"
It works like that because "With Active Sheet" means With the sheet that is active when this line executes, regardless of what else happens before the End With.


Because ActiveSheet is volatile, I avoid it like the plague

Code:

---

    wks .Copy After:=Sheets("Security")
    ActiveSheet.name = "L-" & cboYear

    Set wks = Sheets("L-" & cboYear)
    With wks       
        ....... (More code)

        .Range("O3") = cboYear & " YTD ROI (%):"
        .Range("P1:P3") = "$0.00"
        .Copy After:=Sheets("Security"): ActiveSheet.name = "V-" & cboYear
        .Tab.Color = vbGreen
    End With
 
    Sheets("V-" & cboYear).Tab.Color = vbRed

---

Even your failed attempt in post #1 would work like

Code:

---

        For x = 1 To 2
            ActiveSheet.Copy After:=Sheets("Security")
            If x = 1 Then ActiveSheet.name = "L-" & cboYear
            If x = 2 Then ActiveSheet.name = "V-" & cboYear
        Next

---

This works because the ActiveSheet line is executed twice

The reason it failed the way you wrote it is because there was no sheet name ending in "(3)"

Thank you everyone for the time an explanation

Cheers

Thank you for the explanation SamT.