I have a spreadsheet which I am using as a master document from which I want
to generate many copies of a single worksheet (I only want that particular
worksheet to appear in the copies, not all three worksheets that exist in the
master). The master spreadsheet contains a workbook_open macro which I would
like to include in all the copies.

I am using the sheets(sheetname).copy function to create a clone of the
master worksheet but the workbook_open macro is not getting copied across.
The worksheet also has a number of buttons and all these macros are going
across just fine.

Is it possible to copy the workbook_open macro across in this way? If not
how can I programmatically create a workbook_open macro in the new workbooks?

Thanks in advance

The workbook_open macro does not get copied because you are only copying the
sheets, not workbooks. The buttons work because they belong in the sheets.

To get around this, instead of copying the sheets that you need, you could
try copying the whole book, then removing the sheets that you DON'T need.
That's the easy way.

The hard way (see under creating an Event procedure)
http://www.cpearson.com/excel/vbe.htm

--
Allllen


"Phil N Dank" wrote:

I have a spreadsheet which I am using as a master document from which I want
to generate many copies of a single worksheet (I only want that particular
worksheet to appear in the copies, not all three worksheets that exist in the
master). The master spreadsheet contains a workbook_open macro which I would
like to include in all the copies.

I am using the sheets(sheetname).copy function to create a clone of the
master worksheet but the workbook_open macro is not getting copied across.
The worksheet also has a number of buttons and all these macros are going
across just fine.

Is it possible to copy the workbook_open macro across in this way? If not
how can I programmatically create a workbook_open macro in the new workbooks?

Thanks in advance

Thanks Allllen, very useful references. Unfortunately due to my current level
of skill I wasn't able to make very much progress with either solution :-(
Copying the workbook worked fine in terms of getting the macro across but I
lost context on the master sheet so I couldn't work my way down the cell
range I was using to drive the copies. Anyway I took a look at the
information in the link and it made me rethink what I am actually trying to
achieve and whether there is a simpler, more streamlined approach.

With hindsight, if I can hold the macro in a separate file and copy it in to
the generated sheet then I will achieve my objective and simplify the master
sheet (where the initial macro is not strictly necessary). However when I
copy the exported macro into the generated sheet (from the *.cls file, using
ActiveWorkbook.VBProject.VBComponents.Import) it stores it as a separate
'class' object and it is not run when the workbook opens. Is there a simple
way round this or am I barking up the wrong tree?

Thanks again

"Allllen" wrote:

The workbook_open macro does not get copied because you are only copying the
sheets, not workbooks. The buttons work because they belong in the sheets.

To get around this, instead of copying the sheets that you need, you could
try copying the whole book, then removing the sheets that you DON'T need.
That's the easy way.

The hard way (see under creating an Event procedure)
http://www.cpearson.com/excel/vbe.htm

--
Allllen


"Phil N Dank" wrote:

I have a spreadsheet which I am using as a master document from which I want
to generate many copies of a single worksheet (I only want that particular
worksheet to appear in the copies, not all three worksheets that exist in the
master). The master spreadsheet contains a workbook_open macro which I would
like to include in all the copies.

I am using the sheets(sheetname).copy function to create a clone of the
master worksheet but the workbook_open macro is not getting copied across.
The worksheet also has a number of buttons and all these macros are going
across just fine.

Is it possible to copy the workbook_open macro across in this way? If not
how can I programmatically create a workbook_open macro in the new workbooks?

Thanks in advance

I now have this working Alllen thanks to the very helpful stuff in your guide
on creating an event procedure. I have put the required 'workbook open' code
in a string within the body of the creating macro and it is added to the
'slave' workbooks just prior to the save. The other problem I encountered was
that I wanted to have the workbook protected (to stop curious users
viewing/changing things theydon't need to see) but this prevented the
'workbook open' macro making any changes to the dropdown lists! Doh! For the
moment I have taken the protection off but I will keep looking.

Thanks again for sharing your knowledge and making the community a very
useful resource.

"Phil N Dank" wrote:

Thanks Allllen, very useful references. Unfortunately due to my current level
of skill I wasn't able to make very much progress with either solution :-(
Copying the workbook worked fine in terms of getting the macro across but I
lost context on the master sheet so I couldn't work my way down the cell
range I was using to drive the copies. Anyway I took a look at the
information in the link and it made me rethink what I am actually trying to
achieve and whether there is a simpler, more streamlined approach.

With hindsight, if I can hold the macro in a separate file and copy it in to
the generated sheet then I will achieve my objective and simplify the master
sheet (where the initial macro is not strictly necessary). However when I
copy the exported macro into the generated sheet (from the *.cls file, using
ActiveWorkbook.VBProject.VBComponents.Import) it stores it as a separate
'class' object and it is not run when the workbook opens. Is there a simple
way round this or am I barking up the wrong tree?

Thanks again

"Allllen" wrote:

The workbook_open macro does not get copied because you are only copying the
sheets, not workbooks. The buttons work because they belong in the sheets.

To get around this, instead of copying the sheets that you need, you could
try copying the whole book, then removing the sheets that you DON'T need.
That's the easy way.

The hard way (see under creating an Event procedure)
http://www.cpearson.com/excel/vbe.htm

--
Allllen


"Phil N Dank" wrote:

I have a spreadsheet which I am using as a master document from which I want
to generate many copies of a single worksheet (I only want that particular
worksheet to appear in the copies, not all three worksheets that exist in the
master). The master spreadsheet contains a workbook_open macro which I would
like to include in all the copies.

I am using the sheets(sheetname).copy function to create a clone of the
master worksheet but the workbook_open macro is not getting copied across.
The worksheet also has a number of buttons and all these macros are going
across just fine.

Is it possible to copy the workbook_open macro across in this way? If not
how can I programmatically create a workbook_open macro in the new workbooks?

Thanks in advance