Copy and rename multiple sheets

[av8tordude]

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?



Dim wks as worksheet

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

wks.name = cboYear






here is my failed attempt...



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

[mana]

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

[SamT]

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

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

[av8tordude]

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

Currently...



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)



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

[SamT]

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

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

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
NextThis 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)"

[av8tordude]

Thank you everyone for the time an explanation

Cheers

[av8tordude]

Thank you for the explanation SamT.