Sheet copy

[Guest]

Greetings
My code:

Private Sub CommandButton1_Click()
'

'
Dim sNewSheet As String

sNewSheet = Range("I10").Value
On Error Resume Next
If SheetExists(sNewSheet) = True Then
MsgBox "A requestRequest already exists for" & sNewSheet
Else
Sheets("template").Select
Sheets("template").Copy After:=Worksheets(Worksheets.Count)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
ActiveSheet.Name = Range("i10").Value
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
On Error GoTo 0

End Sub

Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function

What do I need to add so the button is not copied to the new sheet?

Thanks!

 

[Guest]

Sandy,

You are coping the entire sheet. This included any controls on the sheet
and any code modules included on the sheet. I'm not sure if there is a way
to not copy those items when doing a sheet copy, but you can always "post
process."

I assumed that the "template" sheet is the sheet your code is in. The code
below will duplicate the sheet named "template," remove any code in the new
sheet module, and remove the button named "CommanButton1" from the new sheet.
NOTE: You must use the button (NAME) not the Caption.

If the "template" sheet is not the sheet your code is on, or you have code
on that sheet that you want to keep then you can replace "RemoveButton" in
the CommandButton1_Click procedure with
"ActiveSheet.Shapes("CommandButton1").Delete and remove the RemoveButton
procedure.

NOTE: To use the RemoveButton procedure you will need to have a reference to
"Microsoft Visual Basic For Applications Extensibility 5.3." If you don't
know how to do this, visit http://www.cpearson.com/excel/vbe.htm


Option Explicit
Private Sub CommandButton1_Click()
'

'
Dim sNewSheet As String

sNewSheet = Range("I10").Value
On Error Resume Next
If SheetExists(sNewSheet) = True Then
MsgBox "A request already exists for" & sNewSheet
Else
Sheets("template").Select
Sheets("template").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Range("i10").Value
RemoveButton
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
On Error GoTo 0

End Sub
Sub RemoveButton()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long

Set VBCodeMod =
ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
With VBCodeMod
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With
ActiveSheet.Shapes("CommandButton1").Delete
End Sub
Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function

Let me know if you have any questions.

Mike
mike.milligan AT ngc DOT com

 

[Guest]

Thanks
Works great!

Sandy,

You are coping the entire sheet. This included any controls on the sheet
and any code modules included on the sheet. I'm not sure if there is a way
to not copy those items when doing a sheet copy, but you can always "post
process."

I assumed that the "template" sheet is the sheet your code is in. The code
below will duplicate the sheet named "template," remove any code in the new
sheet module, and remove the button named "CommanButton1" from the new sheet.
NOTE: You must use the button (NAME) not the Caption.

If the "template" sheet is not the sheet your code is on, or you have code
on that sheet that you want to keep then you can replace "RemoveButton" in
the CommandButton1_Click procedure with
"ActiveSheet.Shapes("CommandButton1").Delete and remove the RemoveButton
procedure.

NOTE: To use the RemoveButton procedure you will need to have a reference to
"Microsoft Visual Basic For Applications Extensibility 5.3." If you don't
know how to do this, visit http://www.cpearson.com/excel/vbe.htm


Option Explicit
Private Sub CommandButton1_Click()
'

'
Dim sNewSheet As String

sNewSheet = Range("I10").Value
On Error Resume Next
If SheetExists(sNewSheet) = True Then
MsgBox "A request already exists for" & sNewSheet
Else
Sheets("template").Select
Sheets("template").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Range("i10").Value
RemoveButton
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
On Error GoTo 0

End Sub
Sub RemoveButton()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long

Set VBCodeMod =
ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
With VBCodeMod
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With
ActiveSheet.Shapes("CommandButton1").Delete
End Sub
Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function

Let me know if you have any questions.

Mike
mike.milligan AT ngc DOT com