Excel Macros: Creating, positioning and renaming a sheet

[john.galt.online]

I am trying to create a macro that does the following:
1- Creates a new sheet
2- Places the new sheet at the end (extreme right) all all existing
sheets
3- Reads the data written in the cell above the active cell (i.e. cell
that was active when the macro was run), and renames the new sheet to
that text string.

For instance, if
1- there are 5 existing sheets - 1,2,3,4 and 5 (in that order L to R)
2- the button to run the macro is on cell C5 of sheet 1
3- cell C4 has data "aloha"
then, if I hit the button:
a new sheet called aloha should be created to the right of sheet 5

I think the biggest problem I am running into is naming the new sheet
based on the data from a relatively referenced cell.

Please help!

 

[Don Guillett]

try this
Sub makeandnamesheet()
myname = ActiveCell.Offset(-1)
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = myname
End Sub

 

[Bob Umlas]

Sub Answer()
On Error Resume Next
If Activecell.Row=1 then Exit Sub 'can't work as described!
NewName=Activecell.Offset(-1).Value
Worksheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name=NewName
End Sub

Bob Umlas
Excel MVP