VBS Script copy active sheet to two sheets with defined names

[ceytl]

Hello,

I have been trying to write a VBA script to take the active sheet and copy it to two sheets that are name V001 and V2Y

When I run the following error it says the name already exist, and I need to rename it.

Do you know what i am doing wrong?

Sub Add_Sheet()
Copy1
Copy2
End Sub
Private Sub Copy1()

ActiveSheet.Copy After:=Worksheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = "V001"

End Sub
Private Sub Copy2()

Sheets("V001").Copy After:=Sheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = "V2Y"
On Error GoTo 0

End Sub

Thanks!

 

[johnnyL]

How about:

Sub CopyCurrentSheetToTwoDifferentSheets()
'
    Dim CopiesCounter   As Long
    Dim TotalCopies     As Long
    Dim xName           As String
    Dim WS              As Worksheet
'
    Application.ScreenUpdating = False
'
    Set WS = ActiveSheet                        ' <--- Set this to the sheet that you want to copy
'
    On Error Resume Next
'
    Application.DisplayAlerts = False
    Sheets("V001").Delete
    Sheets("V2Y").Delete
    Application.DisplayAlerts = True
'
    WS.Copy after:=Worksheets(Sheets.Count)
    ActiveSheet.Name = "V001"
'
    WS.Copy after:=Worksheets(Sheets.Count)
    ActiveSheet.Name = "V2Y"
'
    Application.ScreenUpdating = True
End Sub

 

[ceytl]

How about:

Sub CopyCurrentSheetToTwoDifferentSheets()
'
    Dim CopiesCounter   As Long
    Dim TotalCopies     As Long
    Dim xName           As String
    Dim WS              As Worksheet
'
    Application.ScreenUpdating = False
'
    Set WS = ActiveSheet                        ' <--- Set this to the sheet that you want to copy
'
    On Error Resume Next
'
    Application.DisplayAlerts = False
    Sheets("V001").Delete
    Sheets("V2Y").Delete
    Application.DisplayAlerts = True
'
    WS.Copy after:=Worksheets(Sheets.Count)
    ActiveSheet.Name = "V001"
'
    WS.Copy after:=Worksheets(Sheets.Count)
    ActiveSheet.Name = "V2Y"
'
    Application.ScreenUpdating = True
End Sub

Thank you!

 

[johnnyL]

you are welcome @ceytl.