Rename Sheet Based On Cell

[leung142]

Hi

i am trying to create a macro that renames a newly created sheet with whatever i paste into A1

 
Sub Example() 
     '
     ' Example Macro
     ' Macro recorded 14/01/2008 by KCHAN
     '
     
     '
    Sheets("Sheet1").Select 
    Sheets("Sheet1").Copy After:=Sheets(4) 
    Sheets("Data Conversion").Select 
    Range("A2").Select 
    Selection.Copy 
    Sheets("Sheet1 (2)").Select 
    Range("A1").Select 
    ActiveSheet.Paste 
    Sheets("Sheet1 (2)").Select 
    Sheets("Sheet1 (2)").Name = "Grapefruit" 
    Range("J29").Select 
End Sub 

Grapefruit is what is pasted into cell A1 but as you can see the sheet is renamed grapefruit regardless of what is actually copied.

Any help is much appreciated

[jlsprink]

Hello,

Maybe changing this line:

Sheets("Sheet1 (2)").Name = "Grapefruit" 

to

Sheets("Sheet1 (2)").Name = Range("A1").Text 

HTH

JL

[Reafidy]

Sub Example() 
     
    Sheets("Sheet1").Copy After:=Sheets(4) 
    With Sheets(5) 
        Sheets("Data Conversion").Range("A2").Copy .Range("A1") 
        .Name = .Range("A1").Value 
    End With 
End Sub