VBA to change name of spreadsheet via a message box

[ariel20029]

Hi, I only have very limited VBA knowledge. Can you please look at the information I have and advise me. I am trying to have excel change the name of a spreadsheet via a messagebox. I copied the below in a module. The spreadsheet I am trying to rename is Template.

thanks so much.
Sharon

Sheets ("Template")
Sheets(Sheets.Count).Name = InputBox("Copy and Paste file ID")

 

[JLGWhiz]

Sheets(Sheets.Count).Name = InputBox("Copy and Paste file ID")

If you want your worksheet named the same as the file name you copy and paste, it should work. It is just as easy to try the code as to ask us. But in any case, you would need a procedure title line, and an end line, eg.

Sub sometingorother()
'code here
End Sub

 

[ariel20029]

If you want your worksheet named the same as the file name you copy and paste, it should work. It is just as easy to try the code as to ask us. But in any case, you would need a procedure title line, and an end line, eg.

Sub sometingorother()
'code here
End Sub

Hi,
Thank you- next problem
Im looking to name the sheet the data that's in Cell E2
Im getting an error message at this point:
Sheets(ActiveSheet.Name) = Range("E2").Select


Any suggestions?

Full Macro below



' Macro1 Macro
Sheets("TEMPLATE").Select
Columns("D:D").Select
Selection.Copy
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("J1").Select
Application.CutCopyMode = False
Selection.Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Sheets(ActiveSheet.Name) = Range("E2").Select
'Sheets(ActiveSheet.Name) = InputBox("Copy and Paste file ID")
ActiveWorkbook.Save
End Sub

 

[JLGWhiz]

Hi,
Thank you- next problem
Im looking to name the sheet the data that's in Cell E2
Im getting an error message at this point:
Sheets(ActiveSheet.Name) = Range("E2").Select


Any suggestions?

Full Macro below



' Macro1 Macro
Sheets("TEMPLATE").Select
Columns("D:D").Select
Selection.Copy
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("J1").Select
Application.CutCopyMode = False
Selection.Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Sheets(ActiveSheet.Name) = Range("E2").Select
'Sheets(ActiveSheet.Name) = InputBox("Copy and Paste file ID")
ActiveWorkbook.Save
End Sub

[COLOR=#0000CD]Sheets(ActiveSheet.Name) = Range("E2").Value[/COLOR]