Best Technique to clone worksheet

[Rich_z]

I have an application that adds worksheets as required by the user.
This includes various graphics objects etc using the following code:


Code:
--------------------

Sub Create_Sheet(Sheet_Name As String)
'*
Dim Which_Arrow As String
'*
Application.EnableEvents = False
Application.ScreenUpdating = False
Sheets.Add.Move after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Sheet_Name
Sheets("Stand.Bound.Wall").Activate
Call Unlock_Sheet("")
'*
'* Find out which direction of lay arrow is invisible and make
'* it visible so it's included in the copy...
'*
If Not ActiveSheet.Shapes(c_Dol_Right_Arrow).Visible Then
Which_Arrow = c_Dol_Right_Arrow
Else
Which_Arrow = c_Dol_Left_Arrow
End If
ActiveSheet.Shapes(Which_Arrow).Visible = True
Cells.Select
Selection.Copy
Sheets(Sheet_Name).Select
ActiveSheet.Paste
ActiveSheet.Shapes(Which_Arrow).Visible = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Stand.Bound.Wall").Select
ActiveSheet.Shapes(Which_Arrow).Visible = False
Call Lock_Sheet
Sheets(Sheet_Name).Activate
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

--------------------


This works fine, however, it does not copy the macros from the source
sheet such as :


Code:
--------------------

Private Sub Worksheet_Activate()
Call Gutter_Activate(ActiveSheet)
End Sub
'*
Private Sub Worksheet_Change(ByVal Target As Range)
Call Gutter_Change(ActiveSheet, Target)
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call Gutter_SelectionChange(ActiveSheet, Target)
End Sub

--------------------


As you can see, the macros themselves are just calls to the actual
routines in a VBA module.

Also what needs to be copied are some custom properties such as:


Code:
--------------------

Public Property Get Angle_R() As Double
Angle_R = Range(Angle_R_Address()).Value
End Property
Public Property Let Angle_R(New_Value As Double)
Range(Angle_R_Address()).Value = New_Value
End Property
Public Property Get Angle_S() As Double
Angle_S = Range(Angle_S_Address()).Value
End Property
Public Property Let Angle_S(New_Value As Double)
Range(Angle_S_Address()).Value = New_Value
End Property

--------------------


What is the best way of achieving a complete clone of the worksheet
including all macros etc?

Regards

Rich

 

[DM Unseen]

Rich,

VBA code is copied over using the VBIDE library which gives access to
all VBA project properties and modules and their contents. This however
also triggers security warnings when you change this through code
(viral behavior), and the VBIDE library might also be not installed
(not standard).

Why not using workbook events or class modules to generalise sheet
events?

DM Unseen

 

[Rich_z]

Cheers DM. I'll have a look into classes. (I'm not a novice programmer
but I'm a relative novice to Excel so expect some more questions!)

Regards

Ric

 

[Tushar Mehta]

You need to use a class only if you use application level events. Of
course, it is a very good idea to do so. Start with XL VBA help for
'Using Events with the Application Object'

It is also a very good idea to separate the data from the code. I, for
one, don't distribute code in workbooks that will also contain data.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[DM Unseen]

Tushar, Rich

Yes, application level events are the most powerful, and you *need* a
class module to get those.(you should study them though, understanding
them is vital to using events in Excel).

But sometimes you do not want those powerful events, just the same
events for a couple of selected sheets (remember unescesarely running
VBA events clears the XL UNDO stack, so do not run an application level
event to catch just one specific things within a few sheets, your UNDO
stack will be constantly cleared!). Then a class module that is
instantiated for just a few sheet wil catch just the events you want,
while not duplicating any code in any worksheet.

DM Unseen