Hi

I need a small piece of code which will transfer the contents of Cell C2
(merged to G2) to the tab at the bottom of the sheet.

Can someone help?

Thanks.



Best Wishes


Colin

Here's one from a past post by Bob Phillips which does it ..
(Note that the file must be saved beforehand. A pre-requisite.)

'---------
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C2" '<===== Change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If


ws_exit:
Application.EnableEvents = True
End Sub
'--------
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Colin Hayes" wrote in message
...

Hi

I need a small piece of code which will transfer the contents of Cell C2
(merged to G2) to the tab at the bottom of the sheet.

Can someone help?

Thanks.



Best Wishes


Colin

Sub missive()
v = Range("C2").Value
ActiveSheet.Name = v
End Sub

--
Gary''s Student - gsnu200714


"Colin Hayes" wrote:


Hi

I need a small piece of code which will transfer the contents of Cell C2
(merged to G2) to the tab at the bottom of the sheet.

Can someone help?

Thanks.



Best Wishes


Colin

Note: Bob's code will not fire if the change of value in C2 is a calculated
value due to a formula.

For that you would need a different type of event code.

Private Sub Worksheet_Calculate()
Me.Name = Range("C2").Value
End Sub


Gord Dibben MS Excel MVP

On Thu, 12 Apr 2007 07:27:10 +0800, "Max" wrote:

Here's one from a past post by Bob Phillips which does it ..
(Note that the file must be saved beforehand. A pre-requisite.)

'---------
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C2" '<===== Change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If


ws_exit:
Application.EnableEvents = True
End Sub
'--------
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Hi

OK thanks guys I got that working fine. Very grateful.

It transfers the contents of C2 to the tab no problem.

I do notice however that when I delete the new value in C2 , that the
tab value stays the same. Is the a way to add a few lines to the code ,
so that the tab name would revert to a fixed title - say 'Oldname' when
C2 is blanked?



Best Wishes


Colin



In article , Max
writes
Here's one from a past post by Bob Phillips which does it ..
(Note that the file must be saved beforehand. A pre-requisite.)

'---------
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C2" '<===== Change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If


ws_exit:
Application.EnableEvents = True
End Sub
'--------
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Hi All

No ideas on this one?

Grateful if you could help.

Best Wishes


Colin





In article , Colin Hayes
writes

Hi

OK thanks guys I got that working fine. Very grateful.

It transfers the contents of C2 to the tab no problem.

I do notice however that when I delete the new value in C2 , that the
tab value stays the same. Is the a way to add a few lines to the code ,
so that the tab name would revert to a fixed title - say 'Oldname' when
C2 is blanked?



Best Wishes


Colin



In article , Max
writes
Here's one from a past post by Bob Phillips which does it ..
(Note that the file must be saved beforehand. A pre-requisite.)

'---------
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C2" '<===== Change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If


ws_exit:
Application.EnableEvents = True
End Sub
'--------
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Might have slipped the radar of responders conversant in vba.
Suggest you try a new posting in .programming
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Maybe...

Option Explicit
Private Sub Worksheet_Calculate()
Dim NewName As String

NewName = Me.Range("c2").Value

If NewName = "" Then
NewName = "OldName"
End If

If LCase(Me.Name) = LCase(NewName) Then
'do nothing
Else
On Error Resume Next
Me.Name = NewName
If Err.Number < 0 Then
Err.Clear
Beep
MsgBox "Can't rename"
End If
On Error GoTo 0
End If

End Sub



Colin Hayes wrote:

Hi All

No ideas on this one?

Grateful if you could help.

Best Wishes

Colin

In article , Colin Hayes
writes

Hi

OK thanks guys I got that working fine. Very grateful.

It transfers the contents of C2 to the tab no problem.

I do notice however that when I delete the new value in C2 , that the
tab value stays the same. Is the a way to add a few lines to the code ,
so that the tab name would revert to a fixed title - say 'Oldname' when
C2 is blanked?



Best Wishes


Colin



In article , Max
writes
Here's one from a past post by Bob Phillips which does it ..
(Note that the file must be saved beforehand. A pre-requisite.)

'---------
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C2" '<===== Change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If


ws_exit:
Application.EnableEvents = True
End Sub
'--------
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

Dave Peterson

Dave,
Thanks for responding. How could your sub be tweaked to also cater for C2
not containing a formula? Ie make it work even if we were to key in or paste
in a value into C2. Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Since the code to do the renaming is gonna be the same, I think I'd separate
that portion to it's own procedure.

Option Explicit
Private Sub Worksheet_Calculate()
Dim myCell As Range
Set myCell = Me.Range("C2")
If myCell.HasFormula = False Then
Exit Sub
End If
Call DoTheRename(myCell)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Set myCell = Me.Range("C2")
If myCell.HasFormula Then
Exit Sub
End If
Call DoTheRename(myCell)
End Sub
Sub DoTheRename(myCell As Range)

Dim NewName As String

NewName = myCell.Value

If NewName = "" Then
NewName = "OldName"
End If

If LCase(Me.Name) = LCase(NewName) Then
'do nothing
Else
On Error Resume Next
Me.Name = NewName
If Err.Number < 0 Then
Err.Clear
Beep
MsgBox "Can't rename"
End If
On Error GoTo 0
End If
End Sub



Max wrote:

Dave,
Thanks for responding. How could your sub be tweaked to also cater for C2
not containing a formula? Ie make it work even if we were to key in or paste
in a value into C2. Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

--

Dave Peterson

HI

Yes that's got it. Works like clockwork.

I combined the two routines , and now the contents of the target cell C2
are transferred to the tab , and then replaced with the text specified
in 'NewName' when C2 is blanked.

This means that the name of the tab can revert to it's previous name
before it was amended.

Perfect - thanks.

Here's how the final code looks :

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C2" '<===== Change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If


ws_exit:
Application.EnableEvents = True

Dim NewName As String

NewName = Me.Range("c2").Value

If NewName = "" Then
NewName = "Audit Results Template"
End If

If LCase(Me.Name) = LCase(NewName) Then
'do nothing
Else
On Error Resume Next
Me.Name = NewName
If Err.Number < 0 Then
Err.Clear
Beep
MsgBox "Can't rename"
End If
On Error GoTo 0
End If

End Sub




Best Wishes


Colin








In article , Dave Peterson
writes
Maybe...

Option Explicit
Private Sub Worksheet_Calculate()
Dim NewName As String

NewName = Me.Range("c2").Value

If NewName = "" Then
NewName = "OldName"
End If

If LCase(Me.Name) = LCase(NewName) Then
'do nothing
Else
On Error Resume Next
Me.Name = NewName
If Err.Number < 0 Then
Err.Clear
Beep
MsgBox "Can't rename"
End If
On Error GoTo 0
End If

End Sub



Colin Hayes wrote:

Hi All

No ideas on this one?

Grateful if you could help.

Best Wishes

Colin

In article , Colin Hayes
writes

Hi

OK thanks guys I got that working fine. Very grateful.

It transfers the contents of C2 to the tab no problem.

I do notice however that when I delete the new value in C2 , that the
tab value stays the same. Is the a way to add a few lines to the code ,
so that the tab name would revert to a fixed title - say 'Oldname' when
C2 is blanked?



Best Wishes


Colin



In article , Max
writes
Here's one from a past post by Bob Phillips which does it ..
(Note that the file must be saved beforehand. A pre-requisite.)

'---------
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C2" '<===== Change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If


ws_exit:
Application.EnableEvents = True
End Sub
'--------
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

I screwed up the _change event:

Option Explicit
Private Sub Worksheet_Calculate()
Dim myCell As Range
Set myCell = Me.Range("C2")
If myCell.HasFormula = False Then
Exit Sub
End If
Call DoTheRename(myCell)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Set myCell = Me.Range("C2")

If Intersect(Target, myCell) Is Nothing Then
Exit Sub
End If

If Target.HasFormula Then
Exit Sub
End If
Call DoTheRename(Target)
End Sub
Sub DoTheRename(myCell As Range)

Dim NewName As String

NewName = myCell.Value

If NewName = "" Then
NewName = "OldName"
End If

If LCase(Me.Name) = LCase(NewName) Then
'do nothing
Else
On Error Resume Next
Me.Name = NewName
If Err.Number < 0 Then
Err.Clear
Beep
MsgBox "Can't rename"
End If
On Error GoTo 0
End If
End Sub


Dave Peterson wrote:

Since the code to do the renaming is gonna be the same, I think I'd separate
that portion to it's own procedure.

Option Explicit
Private Sub Worksheet_Calculate()
Dim myCell As Range
Set myCell = Me.Range("C2")
If myCell.HasFormula = False Then
Exit Sub
End If
Call DoTheRename(myCell)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Set myCell = Me.Range("C2")
If myCell.HasFormula Then
Exit Sub
End If
Call DoTheRename(myCell)
End Sub
Sub DoTheRename(myCell As Range)

Dim NewName As String

NewName = myCell.Value

If NewName = "" Then
NewName = "OldName"
End If

If LCase(Me.Name) = LCase(NewName) Then
'do nothing
Else
On Error Resume Next
Me.Name = NewName
If Err.Number < 0 Then
Err.Clear
Beep
MsgBox "Can't rename"
End If
On Error GoTo 0
End If
End Sub

Max wrote:

Dave,
Thanks for responding. How could your sub be tweaked to also cater for C2
not containing a formula? Ie make it work even if we were to key in or paste
in a value into C2. Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

--

Dave Peterson

--

Dave Peterson

Dave,
Many thanks. It works well.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---