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!! :)
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,692
Office Version
  1. 2016
Platform
  1. Windows
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.
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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.
 

BigDelGooner

Board Regular
Joined
Aug 17, 2009
Messages
197
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.
 

BigDelGooner

Board Regular
Joined
Aug 17, 2009
Messages
197
Thanks p45cal.

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

Have a good weekend!
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,568
Messages
5,572,974
Members
412,491
Latest member
tweetytoon
Top