I have a command button in Sheet3. The code in the backend for this
command button is
Call command1

When the user clicks the button, Sheet3 will copied and renamed as
Sheet4. So, all the objects of sheet3 including the commnand button
will be copied to sheet4. But, I would like to change the code for
this button in sheet4 as
Call command2 instead of being Call command1

Similarly, when the user clicks the button again in sheet3, it will be
copied as sheet5 and the command button should have code
Call command3 instead of being Call command1

Is there any macro that can modify the subroutine slightly? Please let
me know. Thanks

I think this is a very dangerous concept. Adding or modifying code in a
workbook that is already running a macro can cause Excel to crash or
completely lock up.

Maybe if you post your code for Command1, we can help you make it generic,
so that it will work on all worksheets. There is nothing wrong with having
multiple Forms buttons in a workbook that all call the same macro. The
macro just needs to start by setting an object variable to the active
sheet, as follows:

Sub DoStuff()
Dim wsActive as Worksheet

Set wsActive = ActiveSheet

'Perform your actions here.
End Sub

--
Regards,
Bill Renaud

On Nov 1, 11:16 am, "Bill Renaud"
wrote:
I think this is a very dangerous concept. Adding or modifying code in a
workbook that is already running a macro can cause Excel to crash or
completely lock up.

Maybe if you post your code for Command1, we can help you make it generic,
so that it will work on all worksheets. There is nothing wrong with having
multiple Forms buttons in a workbook that all call the same macro. The
macro just needs to start by setting an object variable to the active
sheet, as follows:

Sub DoStuff()
Dim wsActive as Worksheet

Set wsActive = ActiveSheet

'Perform your actions here.
End Sub

--
Regards,
Bill Renaud

Bill, infact I am using activesheet concept. My code is given below..
myformula1 and myformula2 correspond to column A of sheet1.. I want
sheet3 to have this formula but
sheet4 to correspond to column B of sheet1,
sheet5 to correspond to column C of sheet1,
sheet6 to correspond to column D of sheet1... How would I do that? Any
idea on this please? Would select case work? Please let me know..
Thanks

Public Sub command2()
Dim wsActive As Worksheet

Set wsActive = ActiveSheet
Dim shp As Shape
For Each shp In wsActive.Shapes
If shp.Type = msoChart Then
shp.Delete
End If

Next shp

Dim i As Double
Dim j As Double


Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=wsActive.Range("H4"), PlotBy:= _
xlColumns
ActiveChart.SeriesCollection.NewSeries

i = wsActive.Range("F4").Value

j = i + 3

With wsActive
ActiveChart.SeriesCollection(1).XValues = _
.Range(.Cells(2, 1), .Cells(j, 1))
End With
With wsActive
ActiveChart.SeriesCollection(1).Values = _
.Range(.Cells(2, 2), .Cells(j, 2))
End With
'With wsActive '==== Error here so commented the the following
two lines... Rest works well.
'Range("F6").Value =
Application.WorksheetFunction.Sum(.Range(.Cells(2, 2), .Cells(j, 2)))
'End With

ActiveChart.SeriesCollection(1).name = wsActive.Range("A1").Value
ActiveChart.Legend.Select
Selection.Delete
ActiveChart.Location Whe=xlLocationAsObject, name:="Sheet3"
'must be made generic later


'Dim shp As Shape
For Each shp In wsActive.Shapes
If shp.Type = msoChart Then
shp.IncrementLeft -47.25
shp.IncrementTop -1.5
shp.ScaleWidth 1.6, msoFalse, msoScaleFromTopLeft
shp.ScaleHeight 1.46, msoFalse, msoScaleFromTopLeft
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "0.00"
End If


Next shp
End Sub

<<...myformula1 and myformula2 correspond to column A of sheet1...

Where are "myformula1" and "myformula2" in your code?

Also, what to you mean by "to correspond to" in your post?

I made a few slight revisions in your code (mostly to use With statements).
The statement to set Range("F6") to a Sum was missing a period in front of
Range. You still need to take "Sheet3" out somehow, but I don't have your
data, so that I can run the macro to see what it does exactly.

I notice that you delete all of the embedded charts, then basically rebuild
them (or at least rebuild 1 of them). I wonder if that's really necessary.

'----------------------------------------------------------------------
Public Sub command2()
Dim wsActive As Worksheet
Dim shp As Shape
Dim i As Double
Dim j As Double

Set wsActive = ActiveSheet

For Each shp In wsActive.Shapes
If shp.Type = msoChart Then
shp.Delete
End If
Next shp

Charts.Add
With ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=wsActive.Range("H4"), _
PlotBy:=xlColumns
.SeriesCollection.NewSeries
End With 'ActiveChart

With wsActive
i = .Range("F4").Value
j = i + 3

ActiveChart.SeriesCollection(1).XValues = _
.Range(.Cells(2, 1), .Cells(j, 1))
ActiveChart.SeriesCollection(1).Values = _
.Range(.Cells(2, 2), .Cells(j, 2))
.Range("F6").Value = Application.WorksheetFunction _
.Sum(.Range(.Cells(2, 2), .Cells(j, 2)))
End With 'wsActive

With ActiveChart
.SeriesCollection(1).Name = wsActive.Range("A1").Value
.Legend.Delete
.Location Whe=xlLocationAsObject, Name:="Sheet3"
End With 'ActiveChart
'must be made generic later

For Each shp In wsActive.Shapes
If shp.Type = msoChart _
Then
With shp
.IncrementLeft -47.25
.IncrementTop -1.5
.ScaleWidth 1.6, msoFalse, msoScaleFromTopLeft
.ScaleHeight 1.46, msoFalse, msoScaleFromTopLeft
ActiveChart.Axes(xlCategory).TickLabels.NumberForm at = "0.00"
End With 'shp
End If
Next shp
End Sub

--
Regards,
Bill Renaud