change tab to cell name
Posted by Robert on September 02, 2000 8:10 PM
Is it possible to save an Excel workbook with the name of a cell using a Excel macro. I also want to change the tab name to the contents of the cell.
example: cell A1 has the contains 'Data for 01/01/2000' stored in it. I want to save the workbook as 'Data for 01/01/2000' and change the tab to 'Data for 01/01/2000'
Posted by Ivan Moala on September 03, 0100 1:31 AM
Would something like this work for you ??
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim FileNm As String
FileNm = Range("A1").Text
On Error Resume Next
ActiveSheet.Name = FileNm
If Err.Number <> 0 Then Cancel = True: GoTo Errh
On Error GoTo 0
Cancel = True: Application.EnableEvents = False
ThisWorkbook.SaveAs FileNm
Application.EnableEvents = True
Exit Sub
Errh:
MsgBox "Sheet name not valid!" & Chr(13) & "Save canceled"
End Sub
NB: not fully tested and a bit messy...didn't
really look @ coding.....
Ivan
