Open newly created macro from new workbook

**stubarso** · 06-06-2010, 12:57 PM

Hi everyone!! am a newbie to vba but hoping that someone can help as have been struggling with this for the last couple of days. i have created a macro in workbook 'A' that copies data into a template on a new workbook(call it Workbook B). as part of this the first sheet also has a couple of buttons that can print data and do others tasks.

The problem being now that once Workbook A has closed and i go to run the macro from Workbook B it has to reopen Workbook A again to perform the task.
I have tried to add a procedure at the end of the original macro that will then copy it into the new workbook but it still tried to refer to Workbook A.

Is there anyway that i can set the buttons in Workbook B to run macros only from ' This Workbook' ?

ANY HELP WOULD BE GREATLY APPRECIATED!!!

Thanks again

**royUK** · 06-06-2010, 01:16 PM

It's easier to create a template containing the code to copy the data to

**stubarso** · 06-06-2010, 01:27 PM

Thanks for the reply Roy UK. The template is currently based in a sheet in the Master Workbook and as part of the macro it copies the entire sheet, opens a new workbook and pastes it into sheet1 with the buttons already attached to it..

Please Login or Register  to view this content.

the other code i have tried using thinking it may help is:

Please Login or Register  to view this content.

Any other ideas other than having to create a new workbook for template?? just want buttons to only reference 'This Workbook' only!! dmn vba!

**royUK** · 06-06-2010, 01:29 PM

If the code is in the sheet module,attached to the buttons it should move with the sheet.

Attach an example workbook

You can replace this

Please Login or Register  to view this content.

with, and the code in the sheet module will move to the newly created workbook

Please Login or Register  to view this content.

**stubarso** · 06-06-2010, 01:55 PM

i initially didnt have it in the sheet module but a seperate module as a macro and was assigning the macro to the command button. i have changed this now and copied the code into the worksheet module. thanks for the easier way about copying the entire sheet but how do i paste this into my new workbook?!!

**royUK** · 06-06-2010, 02:04 PM

What do you need to paste?

**stubarso** · 06-06-2010, 02:11 PM

the worksheet("opening page") that i copied.

**royUK** · 06-06-2010, 02:13 PM

You've lost me. The line of code will copy the opening page,with code, to a new workbook. Have you tried it?

**stubarso** · 06-06-2010, 02:29 PM

ahh got ya!! sorry. thought i was only replacing the first couple of lines of copying the worksheet.
having the code based in the worksheet module and an activeX command button seems to work over the assigning macro to a form based command button method.. thanks for your help. have been pulling my hair out of this!!

**royUK** · 06-06-2010, 02:30 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save