PDA

View Full Version : Solved: Copy sheet without the vba code



Viko
01-23-2010, 06:30 AM
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
01-23-2010, 09:31 AM
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

Viko
01-23-2010, 12:32 PM
Many thanks!

It does the job!

mdmackillop
01-23-2010, 12:50 PM
Glad to help. You can mark the thread solved using the thread tools dropdown.