How about some alternatives?

If you're creating multiple workbooks that need the same macro, then I wouldn't
want to put copies of the same macro in all those workbooks. When (not if!),
they need to be updated, you'll never be able to find all the files that were
created (and then used to create more!).

Instead, I'd create a single addin file. This would have the macro in it and a
way to run that macro (menubar, toolbar, QAT, ribbon modifications).

When I do this kind of thing, the code can usually run against the activesheet
in any workbook. You may want to add a "are you sure" prompt -- or even check
some indicator on that sheet (a hidden name on that sheet???) before continuing.

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

And Bob Phillips shows a way to use a wrapper so that it can work in both xl2003
and xl2007.
http://msmvps.com/blogs/xldynamic/ar...me-simple.aspx

===================
Alternative #2.

If you have to have code in the new workbooks, then I wouldn't create the code
on the fly. There's user security setting that will stop your code from running
if the user chooses not to allow this kind of code.

Instead you could create a workbook template (*.xlt or *.xltm) that contains all
the code you need. And you could also include any other common details that you
need -- page layout on each sheet, filters, event macros, ...

Then use that template file when you're creating the new workbook.

Option Explicit
Sub testme()

Dim TemplFileName As String
Dim NewWkbk As Workbook

TemplFileName = "C:\path to template\template.xlt"

Set NewWkbk = Workbooks.Add(template:=TemplFileName)

End Sub

You could even protect the template's project (in the VBE) and that may help
keep prying eyes from making (unauthorized) changes to the code.


Michelle wrote:

I want to write a macro that will make a new workbook and I want the new
workbook to have a macro in it.

Ideally, there will be a button on one of the sheets that will run it too.

is that possible?

Can I write a macro that will write a macro?

Thanks

M

--

Dave Peterson