Solved: Copy sheet without the vba code

[Viko]

Hello there,

I have visited this site: rondebruin.nl and have found very useful information on sending emails via vba in excel.

Everything works great, but I need someting that I could not find there.

I need to copy the whole worksheet, becuase I need to send it with its original layout, but not to include the vba code that is attached to this sheet.

Mailing Range or selections does not work for me. I need the whole sheet as it is but wihtout the vba code that goes with the sheet.

Any ideas?

Many thanks to all!

[mdmackillop]

[vba]
Sub DoCopy()
ActiveSheet.Copy
DeleteAllVBA
End Sub

Private Sub DeleteAllVBA()
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=64
'by Smozgur

Dim vbComp As Object

For Each vbComp In ActiveWorkbook.VBProject.VBComponents

With vbComp
If .Type = 100 Then
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
Else
ActiveDocument.VBProject.VBComponents.Remove vbComp
End If
End With
Next vbComp

End Sub
[/vba]

[Viko]

Many thanks!

It does the job!

[mdmackillop]

Glad to help. You can mark the thread solved using the thread tools dropdown.