ps.

Even though I wrote this message thinking that you were saving a single sheet
into a new workbook, it still applies if you're doing a File|SaveAs.

But instead of just looping through the objects on one sheet, you may have to
loop through each sheet, then through all objects on each sheet.

Dave Peterson wrote:

First, if you have procedures that are common to a bunch of workbooks, you may
find putting them in a separate workbook/addin a much better way to organize
them.

You could give the users a toolbar or menu item that allows them to run the
macros.

Another option would be to replace the buttons from the Forms toolbar with
commandbuttons from the control toolbox toolbar. The code behind these
commandbuttons live in the worksheet module and would get copied to the new
workbook along with the sheet.

You'll have to revise your code (slightly) if you want to do that.

Or you could just reassign the correct macro to the current workbook after you
copy the sheet.

With activesheet 'or newwks or what represents the newly copied sheet
.buttons("yourbuttonnamehere").onaction _
= "'" & .parent.name & "'!" & "whatevermacronamehere"
end with

If you have lots, you could even cycle through each button/object and replace
the name of the old workbook with the new workbook.

======
I'd use the addin approach. I wouldn't want to have to update a macro with a
minor change and not have some idea what needs updating.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

dhstein wrote:

I have a workbook called Inventory.xlsm. This has several macros which are
selected with a button. One of them is "PO" which creates a Purchase Order
based on items selected in the Inventory workbook. The button is assigned to
the macro Inventory.xlsm!PO

The macro creates a new workbook called c:\mypath\NewPO.xlsx and saves the
file and closes c:\mypath\NewPO.xlsx

This all works - the file is created successfully.
However, at this point all macro buttons in Inventory.xlsm have been
reassigned so that I can't run them. For example, the PO button is now
assigned to c:\mypath\NewPO.xlsx!PO.

Any ideas are appreciated

--

Dave Peterson

--

Dave Peterson