I have a macro that copies a sheet and renames it to Sheet B.
Sheet B already contains 2 buttons that reference a cell range "B1" in
the button code.
I want to change that cell range to "E1" using a macro.

Is this possible??
Thanks for any help that can be given.

You can using code such as that shown at Chip Pearson's site
http://www.cpearson.com/excel/vbe.htm

However, it would seem easier to me to use a sheet level name and just
change the definition in the new sheet.

Range("B1").Name = "' & Activesheet.Name & "'!Rng1"

then in the code refer to it as

me.Range("Rng1")

After you copy the sheet, with the new sheet active run this command

Range("E1").Name = "' & Activesheet.Name & "'!Rng1"

Now, the button code in the new sheet will refer to E1.

--
Regards,
Tom Ogilvy


"Donna" wrote in message
om...
I have a macro that copies a sheet and renames it to Sheet B.
Sheet B already contains 2 buttons that reference a cell range "B1" in
the button code.
I want to change that cell range to "E1" using a macro.

Is this possible??
Thanks for any help that can be given.

Thanks Tom, Your code gave me an idea to shorten/eliminate the need to
change range B to E which is great but I still need to change some of
the CommandButton Code and I am not fully understanding your code. My
code so far is:-

Private Sub ABC_Click()
d = ActiveSheet.Name
Sheets("ABC").Select
Sheets(d).Visible = False
End Sub

Basically I have a button that activates a sheet and hiding the first
one. This code and button is part of a Master Sheet. I have a main
macro that copies this sheet and renames it. I now want to change the
"ABC" to another sheet name. I have this sheet name in a string (Sht)
(from the main macro) and want to use it to change the code of the
CommandButton.

I don't know if your code can help me do this..if so could you explain
how this is possible.

Thanking you again for your help.

On Chip's page see

Deleting A Procedure From A Module

and

Creating An Event Procedure

An alternative might be to have a public variable that contains the name of
the sheet and you can set it in your code

In the top of the code module for the sheet you will copy, put in the
declaration (outside any procedure)

Public ShName as String

then in the commandbutton click event


Private Sub CommandButton1_Click()
Worksheets(shname).Activate

End Sub

Now when you copy the sheet you can use code like this

Sub CopySheets()
Worksheets("Sheet4").Copy After:=Worksheets _
(Worksheets.Count)
ActiveSheet.Name = "EFGH"
Worksheets("EFGH").shname = "Sheet1"
End Sub

Of course, you would have to initialize ShName in the code module of the
master sheet (possibly in the workbook open event) at some point.


--
Regards,
Tom Ogilvy


wrote in message
ups.com...
Thanks Tom, Your code gave me an idea to shorten/eliminate the need to
change range B to E which is great but I still need to change some of
the CommandButton Code and I am not fully understanding your code. My
code so far is:-

Private Sub ABC_Click()
d = ActiveSheet.Name
Sheets("ABC").Select
Sheets(d).Visible = False
End Sub

Basically I have a button that activates a sheet and hiding the first
one. This code and button is part of a Master Sheet. I have a main
macro that copies this sheet and renames it. I now want to change the
"ABC" to another sheet name. I have this sheet name in a string (Sht)
(from the main macro) and want to use it to change the code of the
CommandButton.

I don't know if your code can help me do this..if so could you explain
how this is possible.

Thanking you again for your help.

Thanks Tom....I'll have a go and see if I can get my head round it!