Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
How to rename a sheet with a new value changing in a cel
Message
 
À
12/07/2005 16:26:17
Information générale
Forum:
Microsoft Office
Catégorie:
Excel
Divers
Thread ID:
01031890
Message ID:
01032176
Vues:
20
>thank you?

I assume you have a sheet and want to rename that sheet to whatever name is in a certain cell. If so you can't do that without some very bizzare and programming that I could tell you how to do, but it is probably more trouble than it is worth.

The trouble is that you'd do it with a macro and the file would have to be open to run the macro, and you could rename the file because it's open.

What you could do is make a macro that gets a value from a cell and saves a new copy of the file with that name. I did something simular once that might help you get started. I deleted some lines that I knew you didn't need. So there may still be some lines I left that are not needed.
In my example I would have to make sure there was a cell named "Logon" in the "Instructions" sheet for this to work.

Hope it helps and let me know if you have any questions.
Sub SaveTemplate()
    SetVars
    Dim NameString As String
    
    SheetName = ActiveSheet.Name
    Sheets("Instructions").Select
    Range("Logon").Select
    logon = ActiveCell.Value
    NameString = "C:\Documents and Settings\" + logon + _
                 "\Application Data\Microsoft\Templates\SeedSheet.xlt"
    Application.DisplayAlerts = False 'Turn off file overwrite warnings
    On Error GoTo ErrorHandler
    ActiveWorkbook.SaveAs FileName:=NameString _
        , FileFormat:=xlTemplate, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    On Error GoTo 0
    Sheets(SheetName).Select
    iAnswer = MsgBox("Your preferences have been saved and will be the default " + CRLF + _
    "values the next time this spreadsheet template is opened.", vbInformation, "Info.")
    'ActiveWorkbook.Close
    Application.DisplayAlerts = True
    Exit Sub
ErrorHandler:
    Sheets(SheetName).Select
    iAnswer = MsgBox("Unable to save the file for some reason.", vbExclamation, "Could Not Save File")
    On Error GoTo 0
End Sub
___________________________
Kenneth Wonderley
http://www.wonderley.com


...the fruit of the Spirit is love, joy, peace, patience, kindness, goodness, faithfulness, gentleness and self-control.
Galatians 5:22 & 23
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform