>The easiest course of action would be to delete the copied CommandButtons
For Code see
http://www.rondebruin.nl/controlsobjectsworksheet.htm
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Carlee" <(E-Mail Removed)> wrote in message news:881107CE-8737-4F1E-A68D-(E-Mail Removed)...
>I use the following code to copy the active sheet to a new workbook and
> attach the file to an email.
>
> Problem: How can I prevent the command buttons from being copied into the
> new sheet. Can anyone help me out?
>
>
> Sub Mail_ActiveSheet()
> 'Working in 97-2007
> Dim FileExtStr As String
> Dim FileFormatNum As Long
> Dim Sourcewb As Workbook
> Dim Destwb As Workbook
> Dim TempFilePath As String
> Dim TempFileName As String
>
> With Application
> .ScreenUpdating = False
> .EnableEvents = False
> End With
>
> Set Sourcewb = ActiveWorkbook
>
> 'Copy the sheet to a new workbook
> ActiveSheet.Copy
> Set Destwb = ActiveWorkbook
>
> 'Determine the Excel version and file extension/format
> With Destwb
> If Val(Application.Version) < 12 Then
> 'You use Excel 97-2003
> FileExtStr = ".xls": FileFormatNum = -4143
> Else
> 'You use Excel 2007
> 'We exit the sub when your answer is NO in the security dialog
> that you only
> 'see when you copy a sheet from a xlsm file with macro's
> disabled.
> If Sourcewb.Name = .Name Then
> With Application
> .ScreenUpdating = True
> .EnableEvents = True
> End With
> MsgBox "Your answer is NO in the security dialog"
> Exit Sub
> Else
> Select Case Sourcewb.FileFormat
> Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
> Case 52:
> If .HasVBProject Then
> FileExtStr = ".xlsm": FileFormatNum = 52
> Else
> FileExtStr = ".xlsx": FileFormatNum = 51
> End If
> Case 56: FileExtStr = ".xls": FileFormatNum = 56
> Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
> End Select
> End If
> End If
> End With
>
>
> 'Save the new workbook/Mail it/Delete it
> TempFilePath = Environ$("temp") & "\"
> ' TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now,
> "dd-mmm-yy h-mm-ss")
> TempFileName = "Report from " & Sourcewb.Name & " " & Format(Now,
> "dd-mmm-yy h-mm-ss")
>
>
> With Destwb
> .SaveAs TempFilePath & TempFileName & FileExtStr,
> FileFormat:=FileFormatNum
> On Error Resume Next
> .SendMail "", _
> "Sample Tracking Reports"
> On Error GoTo 0
> .Close SaveChanges:=False
> End With
>
> 'Delete the file you have send
> Kill TempFilePath & TempFileName & FileExtStr
>
> With Application
> .ScreenUpdating = True
> .EnableEvents = True
> End With
> End Sub
>
> --
> Carlee
Similar Threads
