Add a Macro to a New Workbook Using VBA

**Allienne** · 01-21-2019, 01:30 PM

Hello!

This is a very frustrating macro I am working on.

So now I need to find a way to copy or create a new module and macro in a new workbook using vba. So I need to create a button to update data but I need to add the macro to the new workbook and tie it into the button. So I can create the button but I can't figure out how to add or copy a macro from the original workbook to the new workbook or to create just the Sub I need from the original workbook to the new workbook.

Any help would be great!

**Richard Buttrey** · 01-21-2019, 01:57 PM

Hi,

Does the following help

Please Login or Register  to view this content.

**Allienne** · 01-21-2019, 02:18 PM

Richard,

The issue I have is that the macro I am running creates the new workbook but it will not be saved at that time as it is not a localized document. So I'm not sure how to select the New Workbook Name as it can vary depending on if other workbooks were run or if this is run twice. So it may be Book1, Book2, etc.

Thanks!

**Arkadi** · 01-21-2019, 02:53 PM

@Allienne You could set the new workbook as a workbook object at time of creation and then pass it on as a parameter to the Sub CopyModule() sub. That way you don't need the name anymore.

@Richard sorry for jumping in on something you have handled, I know I usually don't post much after hours and since you are in the UK I thought you might not be on again today.

**Allienne** · 01-21-2019, 02:54 PM

How do I set a workbook object?

**Arkadi** · 01-21-2019, 02:59 PM

How are you creating the "new" workbook?
Set wbNew = Workbooks("your new workbook name") is the line of code where Richard Sets the workbook object, where wbNew becomes the workbook that has the name in the quotes.

Please Login or Register  to view this content.

would make the new workbook wbNew, then depending on how you have integrated Richard's code (Call CopyModule?) you could change it to:

Please Login or Register  to view this content.

And then just comment out Set wbNew = Workbooks("your new workbook name")

**Allienne** · 01-21-2019, 03:04 PM

I'm creating the workbook by moving a Sheet from the original workbook to a new workbook.

**Arkadi** · 01-21-2019, 03:07 PM

Ok I assume that is workheets("sheet name").copy?
When you copy the sheet, I presume (as is normal) that the new book is the active one as soon as it gets created. So instead of

Please Login or Register  to view this content.

you could, next line after the copy, put:

Please Login or Register  to view this content.

**Allienne** · 01-21-2019, 03:15 PM

For some reason, I keep getting Compile error: User-defined type not defined for the

Please Login or Register  to view this content.

**Arkadi** · 01-21-2019, 03:45 PM

In the VBA editor, go to Tools->References and put a checkmark next to Microsoft Visual Basic for Applications Extensibility x.x
In my case x.x is 5.3 (office 2016) not sure if your version number is the same but that should not matter.

**Allienne** · 01-21-2019, 03:53 PM

I don't have that option. It is greyed out for me.
reference greyed out.jpg

**Allienne** · 01-21-2019, 03:59 PM

I got it to work, I had to restart the VB Editor. But now it says Run-time error '1004': Programmatic access to Visual Basic Project is not trusted. no idea what that means.

**Arkadi** · 01-21-2019, 04:05 PM

I was going to say.... you need to stop code from running, can't add it while it is in break mode (meaning error line still highlighted).

For your next issue. In Excel, go to File -> Options -> Trust Center
Click on Trust Center Settings, then choose Macro Settings
There put a checkmark next to "Trust access to the VBA project object model"

**Allienne** · 01-21-2019, 04:18 PM

It works!!

Thank you so much!!

**Arkadi** · 01-21-2019, 04:24 PM

Yay!

Thanks for marking the thread as solved, and letting us know how it went

Add a Macro to a New Workbook Using VBA

LinkBack

Thread Tools

Rate This Thread

Display

Add a Macro to a New Workbook Using VBA

Re: Add a Macro to a New Workbook Using VBA

Re: Add a Macro to a New Workbook Using VBA

Re: Add a Macro to a New Workbook Using VBA

Re: Add a Macro to a New Workbook Using VBA

Re: Add a Macro to a New Workbook Using VBA

Re: Add a Macro to a New Workbook Using VBA

Re: Add a Macro to a New Workbook Using VBA

Re: Add a Macro to a New Workbook Using VBA

Re: Add a Macro to a New Workbook Using VBA

Re: Add a Macro to a New Workbook Using VBA

Re: Add a Macro to a New Workbook Using VBA

Re: Add a Macro to a New Workbook Using VBA

Re: Add a Macro to a New Workbook Using VBA

Re: Add a Macro to a New Workbook Using VBA

Thread Information

Users Browsing this Thread

Similar Threads

Can a Macro in a Workbook open and run a macro found in the other Workbook

Personal Macro Workbook interference with other workbook macro(s)

Interference with Personal Macro Workbook macros and other workbook macro(s)

[SOLVED] VBA to open new workbook and run macro from new workbook, which closes old workbook

[SOLVED] Macro to find data in source workbook and copy paste to target workbook

[SOLVED] Save the split the workbook file type as Excel Binary Workbook From Run Macro

Button macro that copies and pastes data from workbook to workbook: Ranged Column Q?

Bookmarks

Bookmarks

Posting Permissions