Copying macro and associated button to new file/sheet "in flight"

[Asterix]

Dear all,

macro1 and macro2 are held on .xls1.

Macro 1 creates a new file (.xls2), collects data from a given directory, manipulates and formats it.

Macro2 extracts individual specific items from .xls2 and emails that extract to an individual.

Problem is, macro2 starts off existing in .xls1, attached to a button.

.xls1 isn't available to the person that .xls2 is created for.

At the end of macro1, how do I copy macro2 and it's associated button into the top left hand corner of .xls2?

This and other forums like it have been invaluable for getting me to this point. All the other questions I've had, I've searched for answers and found them. I've searched for this one too, just can't find an answer.

Thanks for looking.

[Bob Phillips]

Use a control toolbox button with the code in the worksheet code module, and just copy the sheet to the new book.

[Asterix]

Thanks , that would work and I'll start by going down that route but [aaarghhh!...scope creep ], it would be more cosmetically pleasing if the button just appeared in the newly created sheet on the newly created file.

[Bob Phillips]

Just delete the others, then it does!

[Asterix]

I think we're closer but it's me not being clear enough and my lack of knowledge around adding/ identifying/ copying buttons...here's the original post amended to clarify...

macro1 and macro2 are held on .xls1. Button1 and Button2 are associated with macro1 and macro2 respectively on sheet1 of .xls1

Macro 1 creates sheet2 on .xls1, collects data from a given directory, manipulates and formats it. Then macro1 takes sheet2 and moves it to a newly created date/time-stamped-in-the-filename .xls2

Macro2 extracts individual specific items from sheet2 on .xls2 and emails that extract to an individual.

Problem is, macro2 starts off existing in .xls1, attached to button2.

.xls1 isn't available to the person that .xls2 is created for.

Does that make sense?

If I did what you suggest (which sounds completely valid), I'd still have to delete button1 (and preferably macro1) from sheet2...and I don't know how to identify and delete buttons.

[Bob Phillips]

post your workbook and we'll see what we can do.

[Norie]

Do you really need 2 seperate buttons?

[Asterix]

post your workbook and we'll see what we can do.

I'd love to, but that would contravene our security policy (financial industry).

[Asterix]

Do you really need 2 seperate buttons?

Yep, they do 2 separate things - wouldn't be desirable to combine them, even with a user confirm breakpoint.

[Bob Phillips]

I'd love to, but that would contravene our security policy (financial industry).

Can you not even post an obfuscated workbook?

[Asterix]

Can you not even post an obfuscated workbook?

I could, but you may not hear from me again while I'm mid-obfustication. Will try and do this tomorrow. Thanks again for persevering.

[Asterix]

Have obfusticated completely.

Hope I've attached it right. Sheet opens with 2 buttons, "Setup" and "Email". "Email" is effectively a dummy for illustration purposes. Select "Setup" and a new sheet is created, and then moved to a newly created file with specific date/time title stamp.

I want to be able to get the "Email" button and associated macro into the new sheet (preferably before for simplicity but after the move will do). I do not want the "Setup" button and macro in the new sheet.

N.b. I had to "Save" the attachment. "Open" it caused a bug for me.

Thanks.

[Asterix]

[said in a smily tone, appreciative of all the comments so far]
Based on the speed of response to the original question, am I to assume we're having a "hmmm", stroky beard moment, then?

[Asterix]

Ok, have worked out how to move all macro's but not 1 macro of several, nor how to setup a button on a new sheet...yet

[Asterix]

http://www.vbaexpress.com/kb/getarticle.php?kb_id=275

[Asterix]

Except I'm having problems assigning the existing macro that I've moved (i.e. is already present in the new file) to the button that this code creates.

[Bob Phillips]

Now I want you to sit down, I have some extremely disconcerting newqs for you.

We are NOT all sitting here waiting for you to post another question that we can rush off and solve for you.

[Asterix]

Now I want you to sit down, I have some extremely disconcerting newqs for you.

We are NOT all sitting here waiting for you to post another question that we can rush off and solve for you.

Haha...I knew that, but thanks for pointing out the bleedin' obvious anyway (note to self, must use more smiley's next time)

Anyway, courtesy of you, other VBAexpress threads, Wilmot forums and drexcel, it's done (well enough for me anyway), see attachment. Now, if only I could get rid of that pesky "Click for action" button which I created whilst testing one of the threads that didn't work (for me).

Thanks all.

[Asterix]

d'uh...the school of basic emailing applies

[Bob Phillips]

[vba]

Public Sub DeleteDamnButton()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
obj.Delete
Next obj
End Sub
[/vba]