Delete code when copying

**Bob@Sun** · 01-23-2010, 12:52 PM

I am using the code below to copy a sheet to new file end send via email. As I have some vba code in the sheet which I am coping I need to delete before sending.

Please Login or Register  to view this content.

I found on the net that if I add this line to the code, it will delete the vba from the sheet

Please Login or Register  to view this content.

But it keeps giving me an error.

Any ideas?

**royUK** · 01-23-2010, 01:04 PM

What error?

Have you checked the References - VBA Extensibililty library?

Is the VBA Project locked?

**Bob@Sun** · 01-23-2010, 01:15 PM

I did not check the References - VBA Extensibililty library and do not know what to check

Please explain

Also, I am not sure where exactly should I add those lines. I added then in this part of the code

Please Login or Register  to view this content.

The error is Object doesn't support this property or method

**Richard Buttrey** · 01-23-2010, 01:17 PM

Hi,

Further to Roy's suggestion about checking that in the VBA Tools References, you have the VBA Applications Extensibility checked, you may find the following useful. It deletes all VBA code. (From Chip Pearson's admirable web site).

Please Login or Register  to view this content.

HTH

**Bob@Sun** · 01-23-2010, 01:21 PM

Richard,

Can you please tell how can integrate the code that you provided in my code?

I am having dificulties with that

Thanks

**Richard Buttrey** · 01-23-2010, 01:32 PM

Hi Bob,

Probably the easiest way is to add the procedure DeleteAllVBACode() as a completely separate procedure and then call it from your existing macro. So immediately under the two lines of code in your macro which create the copy of the sheet as a new workbook, viz.

Please Login or Register  to view this content.

add the line:

Please Login or Register  to view this content.

At this point your existing macro will break out to the DeleteAllVBACode macro, delete the code in the copy workbook and then return to your macro.

HTH

**Bob@Sun** · 01-23-2010, 01:48 PM

I did as you sad. I have The Microsoft Visual Basic for Application Extensibility 5.3 check and now
I am getting error here:

Please Login or Register  to view this content.

Run-time error 5

Invalid procedure call or argument

**Bob@Sun** · 01-23-2010, 02:10 PM

I got it to work!

Please Login or Register  to view this content.

Thanks for the ideas!

**Topazia** · 07-05-2010, 07:13 PM

Hi, I tried to use your code in mine:
Set Destwb = ActiveWorkbook
Destwb.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.DeleteLines 1, _
Destwb.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.CountOfLines

I am trying to copy a sheet and delete the code already in it. But it always gives me an error: compile error, expcted end property?
Do you have any idea?
Thanks
Topazia

**shg** · 07-05-2010, 07:32 PM

Welcome to the forum.

Please take a few minutes to read the forum rules, and then start your own thread. When you do, use CODE tags for code.

Thanks.