Saving a sheet as a cell name, and then returning to another sheet.

[Jo4x4]

Hi, I have a sheet with a few teams and their scores. I would like to create a separate sheet with each teams scores. They all need to be in the same workbook.

I have played around with some coding I got off the net. I managed to get the tab(sheet) renamed to one of the cell blocks. I also managed to get a save and return button going. The problem is that they don't work together. Ideally, I would like to select a team on my "team master" sheet, get all their info, and then save that sheets as another sheet named after the selected team. It should then return me to my "team master" sheet.


I have uploaded a sample here: http://rapidshare.com/files/436828790/Teams.xls

 

[xld]

Try

[pre]
Private Sub Saveandreturn_Click()
Dim sh As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

With Me

On Error Resume Next
.Parent.Worksheets(.Range("C2").Value).Delete
On Error GoTo 0
Set sh = .Parent.Worksheets.Add(After:=.Parent.Worksheets(.Parent.Worksheets.Count))
sh.Name = .Range("C2").Value
.Cells.Copy sh.Range("A1")
sh.UsedRange.Value = sh.UsedRange.Value
End With

Me.Activate

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
[/pre]

 

[Jo4x4]

Brilliant stuff!


Thanks a stack!

Jo