Sheet name link to cell

  • Thread starter Thread starter chris
  • Start date Start date
C

chris

I am using the following code to link the name of a worksheet to a cell:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim ws2 As Worksheet
Dim cell As Range

If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
On Error Resume Next
Set ws2 = Sheets("Current")
Set cell = Sheets("Totals").Range("A1")
ws2.Name = cell.Text
End Sub

----------

This works great great for cell A1 but I can't get it to use any other cell.
If I change it to A2 or B1 it doesn't work. What I would like to be able to
do is use the last used cell in column A. Also is it possible for this to
not take effect until I push a button or something. In other words, I want
sheet "Current" to be called "Current" until I run a macro. Currently, I
have the code attached to the "Totals" sheet.

Any help would be greatly appreciated.

Chris
 
OK. I was stupid... I was only changing the second A1 reference. I didn't
notice the first one. I still can't figure out the other two questions
though.

Chris
 
why not just

Sub chgname()'assign to a button/shape
x = Cells(65536, 1).End(xlUp)
Sheets("sheet9").Name = x
End Sub
 
Thank you for the reply. Unfortunately I am brand new to this stuff. Can
you give a little more detail on how to do this? I would appreciate it.

Chris
 
How to do what, exactly.

chris said:
Thank you for the reply. Unfortunately I am brand new to this stuff. Can
you give a little more detail on how to do this? I would appreciate it.

Chris

this Currently,
Click to expand...
 
Back
Top