How can I copy VBA module to new worksheet?

BigDelGooner

Board Regular
Joined
Aug 17, 2009
Messages
197
I am coping a few worksheets into a new workbook using the following code (which works fine)...

Sheets(Array("Data", "Lists", "Summary", "BarChart", "ErrorChart", "Datastream_Data")).Copy

The problem now is that these sheets use code from a VBA module in the orginal workbook. Is is possible to copy the module (but keeping the orginal) into the new workbook with the copied worksheets?

Please help!! :)
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Open both workbooks, then go into the VBA screen, look at the project window on the left hand side, and extend the folder with the module in it that has your sub routine on it, then simply grab the module and walk it into the other workbook in the module screen it will then create a copy for you, save the workbooks and it done.
 
Upvote 0
Another way (since you're using vba to do the copying), is to copy the whole workbook (.SaveAs?, .SaveCopyAs?) and remove the sheets you don't want from that copy.
 
Upvote 0
Thank you v much for the responses guys.

I was trying to keep this within the code (rather than dragging and dropping the module manually) so will try p45cal's answer.

I was kind of hoping that it would be a relatively simple line of code, something like workbooks(wbk).module("Module1").copy.
 
Upvote 0
Thanks p45cal.

Just putting the finishing touches to the code now and saveas option definitely working well, so thank you.

Have a good weekend!
 
Upvote 0
No references needed (late binding), but trusted access to vba components must be allowed (manual or admin setting). Managed to shorten it to this sort of thing:
Code:
Sub CopyVBAModule()
FName = "SploshSplashSplish.bas"
ActiveSheet.Copy
Set ThatWorkbook = ActiveWorkbook
ThisWorkbook.VBProject.VBComponents("module1").Export FName
ThatWorkbook.VBProject.VBComponents.Import FName
Kill FName
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top