Copy VBA Modules To Another Workbook

**strato1983** · 05-11-2010, 01:31 PM

I've been searching the net and though found several variations none seem to work.
What I need is a code to add to an existing macro that would copy all the VBA modules from the Source Database (Sourcedb) to the Destination database (Destdb). Can anyone help?

**royUK** · 05-11-2010, 01:41 PM

Why do you need to do this? If you do this with code you cannot protect your VBA Project.

Chip Pearson's article should be what you want

**strato1983** · 05-11-2010, 01:53 PM

My database copies selected worksheets from the sourcedb to a destdb then emails them to other users. There are command buttons on each copied worksheet that link to VBA modules but do not work in the Destdb because the modules from the Sourcedb are not available. I'm not sure by what you mean about protecting the VBA project. Can I assume that the code will then be available to those who view the copied projects?

**royUK** · 05-11-2010, 01:56 PM

If you don't protect the code anyone can view it.

It would be much simpler to create a copy of the workbook& delete unneeded sheets

**strato1983** · 05-11-2010, 02:11 PM

I'm not concerned about viewing of the VBA since the source db cannot be changed.

Only 1 or 2 selected worksheets need to be copied and emailed at any giving time and there could be 50 sheets or more left to delete after the copy, and since the users are very novice in Excel my VBA only copies the selected sheets to a new workbook and attaches it to an email. The problem is that without the modules the needed worksheet command buttons that are also copied don't work.

**royUK** · 05-11-2010, 02:21 PM

I've told you the easiest way,you can automate it using VBA.

**strato1983** · 05-11-2010, 02:40 PM

I fully understand how easy it is to copy the workbook then delete the unneeded worksheets manually but how would VBA automatically delete the sheets you don't want? Could VBA delete all the sheets that were not selected but keep the ones that were?

Can you provide a example?

**shg** · 05-11-2010, 02:43 PM

If the code is in the sheet modules, then you can create a new workbook from the sheet template, and then add additional sheets from the same template as necessary.

Then copy the data from the original workbook to the new sheets and email.

**strato1983** · 05-11-2010, 02:56 PM

I see where you are coming from but the procedure you are suggesting would require the user to add worksheets as needed, this is a function that most users I email cannot perform. So! this is what I have and not have in a single VBA module that is controlled by a command button on each sheet.

1. DONE - Copy ONLY the selected worksheets to a new workbook
2. NEEDED - Copy all modules to the new workbook (so commands will work with the new workbook)
3. DONE - Rename and attach workbook to an email