PDA

View Full Version : Any way to rename Sheet1 to the name of the Workbook when you save?



MrSteve
04-22-2008, 07:06 AM
Hello

Does anyone know any VBA I can use to rename Sheet1 to the name of the Workbook when I save?

For example, I create a new workbook. I click File -> Save As -> And save it as "Hello.xls".

I would like Sheet1 in Hello.xls to automatically be renamed to Hello.

Is this possible?

Thank you!

Bob Phillips
04-22-2008, 07:20 AM
Try this



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sFile
If SaveAsUI Then
Application.EnableEvents = False
Cancel = True
sFile = Application.GetSaveAsFilename("","Excel Files (*.xls), *.xls")
If sFile <> False Then
sFile = Right(sFile, Len(sFile) - InStrRev(sFile, "\"))
ThisWorkbook.Worksheets(1).Name = Replace(sFile, ".xls", "")
ThisWorkbook.SaveAs sFile
End If
End If
Application.EnableEvents = True
End Sub

MrSteve
04-22-2008, 07:27 AM
Works perfectly :)

Thank you xld!