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

Jim885

Well-known Member
Joined
Jul 8, 2012
Messages
663
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?

VBA Code:
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
826
Office Version
  1. 365
Platform
  1. Windows
I note that variable exists is neither declared nor set, unless it's global.

Replace your block of code with this:

VBA Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
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

Well-known Member
Joined
Jul 8, 2012
Messages
663
Thank you both.
Both solutions work.

Jazzer,
Good catch. Yes, variable was off because I typed it wrong in the post.
VBA Code:
sheetName = ws1.Range("A2").Value & "-" & ws1("Master").Range("E2")
should have been
VBA Code:
sheetname = ws1.Range("A2").Value & "-" & ws1.Range("E2").Value
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Thanks for the feedback,
Regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,128,135
Messages
5,628,901
Members
416,352
Latest member
Lunox01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top