Copy one sheet to another without the ActiveX buttons and without the vba code behind the worksheet

[Jim885]

I'm using the following code that works well except that it also transfers the code behind the original Master worksheet with it to the newly added worksheet, as well as all the ActiveX controls (two command buttons).
This is not my desired outcome. I just want the data. I noticed that even using value = value still does not remove the code.

Can someone assist?

Sub CreateSheetWithName()
Dim ws As Worksheet
Dim sheetName As String
Set ws1 = ThisWorkbook.Sheets("Master")
sheetName = ws1.Range("A2").Value & "-" & ws1("Master").Range("E2")
For i = 1 To Worksheets.Count
    If Worksheets(i).Name = ws1.Range("A2").Value & "-" & ws1.Range("E2").Value Then
    End If
Next i
If Not exists Then
    ws1.Copy after:=Worksheets(Sheets.Count)
    Sheets(i).Name = sheetName
    'ActiveSheet.Name = ws1.Range("A2").Value & "-" & ws1.Range("E2").Value  ' accomplishes the same as the line above
        Sheets(sheetName).Shapes("Rounded Rectangle 3").delete
End If

ws1.Activate

End Sub

 

[6StringJazzer]

I note that variable exists is neither declared nor set, unless it's global.

Replace your block of code with this:

Dim NewSheet As Worksheet '''' can go anywhere but I suggest the top of the Sub
If Not exists Then
   Set NewSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
   NewSheet.Name = sheetName
   ws1.UsedRange.Copy NewSheet.Range("A1")
End If

 

[JLGWhiz]

Sub t()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Sheets("Master")
SheetsAdd After:=Sheets(Sheets.Count)
ws1.UsedRange.Copy
Sheets(Sheets.Count).Range("A1").PasteSpecial xlPasteValues
Sheets(Sheets.count).Name = ws1.Range("A2").Value & ws1.Range(("E2").value
End Sub

 

[Jim885]

Thank you both.
Both solutions work.

Jazzer,
Good catch. Yes, variable was off because I typed it wrong in the post.

sheetName = ws1.Range("A2").Value & "-" & ws1("Master").Range("E2")

should have been

sheetname = ws1.Range("A2").Value & "-" & ws1.Range("E2").Value

 

[JLGWhiz]

Thanks for the feedback,
Regards, JLG