A Workbook and Macro Structuring Problem

[MikeG]

I have a workbook and in sheet1, cell A5, the user specifies a desired file name, e.g "Quote - March 12." They can then push a command button and then the associated macro makes a copy of sheet1 and then saves this newly created workbook using the file name the user defined.

Now the tricky part. Later on, I want the user to be able to open the new workbook, edit it, update the file name, e.g. to "Quote - June 4" and then push a button that will save the workbook using the updated name.

My problem is that I'm not sure how to "pass" a macro from the original workbook that will then be available in the new workbook so that the user can save the new workbook by just pressing the button.

Is it possible to transmit a macro to a new workbook this way? When I first tried, there were none of the macros from the original workbook in the new one.

I would welcome any tactical or strategic help.

Thanks,

Mikeg

 

[AMAS]

Hi,

When saving, is the macro creating a new workbook and copy/pasting the worksheet in the new workbook? Or is it creating a saveas copy of the original workbook?

AMAS

 

[MikeG]

Hi,

When saving, is the macro creating a new workbook and copy/pasting the worksheet in the new workbook? Or is it creating a saveas copy of the original workbook?

AMAS

Thanks AMAS

So the user starts with a basic "Blank Template" worksheet in the original workbook. They specify a new file name and the worksheet is copied into a new workbook and saved under that name.

From then on, the new workbook cuts all ties with the original. But I want the new workbook to have a macro that will "save itself" under the user defined name.

Mikeg

 

[MikeG]

Perhaps an analogy is as follows:

The Captain on a ship holds a naming ceremony for a lifeboat.

He then, for whatever reason, selects three crew members, puts them in the lifeboat and casts them into the sea.

As the lifeboat drifts away, the Captain shouts out, "By the way, if you want to rename the lifeboat, I left the instructions in the rear storage bin."

Can VBA do the same thing as the Captain.

Thanks,

Mikeg

 

[AMAS]

I have no experience in this, but you did job my memory about a similar question that someone posted a while back. The suggestion (that I recall) to look at CPearson's site (http://www.cpearson.com/Excel/VBE.aspx) where he describes this in detail.

Sorry I couldn't be of more help.

AMAS

 

[MikeG]

I have no experience in this, but you did job my memory about a similar question that someone posted a while back. The suggestion (that I recall) to look at CPearson's site (http://www.cpearson.com/Excel/VBE.aspx) where he describes this in detail.

Sorry I couldn't be of more help.

AMAS

Thanks Amas

Mikeg