Hi,
I'm trying create a copy of the active sheet and name it based on a cell value ("H1" is text January) using a command button.
This works fine. But the problem i run into is that when the name already exist I get an error 1004. Then it creates a copy of the sheet name like "test (2)".
What I want is when this value already exist in the sheets, it should not make a copy an no error.
So only make a copy if the value not exists.
This is the code:
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set wh = Worksheets(ActiveSheet.Name)
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
If wh.Range("H1").Value <> "" Then
ActiveSheet.Name = wh.Range("H1").Value
ActiveSheet.Shapes("CommandButton1").Delete
End
End If
wh.Activate
End Sub
Thanks in advance!
Michael
I'm trying create a copy of the active sheet and name it based on a cell value ("H1" is text January) using a command button.
This works fine. But the problem i run into is that when the name already exist I get an error 1004. Then it creates a copy of the sheet name like "test (2)".
What I want is when this value already exist in the sheets, it should not make a copy an no error.
So only make a copy if the value not exists.
This is the code:
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set wh = Worksheets(ActiveSheet.Name)
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
If wh.Range("H1").Value <> "" Then
ActiveSheet.Name = wh.Range("H1").Value
ActiveSheet.Shapes("CommandButton1").Delete
End
End If
wh.Activate
End Sub
Thanks in advance!
Michael