Renaming Multiple Worksheets

[alyktre]

Hello,

I have a workbook full of order sheets, I am attempting to figure out a code that will automatically generate the name of sheet based on one cells' value (ex. W9). However; if they cell should be empty, I would like it to keep the name as Sheet1 or Sheet2, etc. Attempted to find sources of similar strings online, not having much luck and feeling like my brain might explode. Any ideas?

Cheers,
Aly

 

[Caleeco]

Hello Alyktre,

Would something like this work for you?

Sub Renamesheets()


Dim ws As Worksheet


For Each ws In Worksheets
    If ws.Range("W9") <> "" Then
        ws.Name = ws.Range("W9")
    End If
Next ws


End Sub

Regards
Caleeco

 

[alyktre]

Is there a way for it to auto run without having to open VBA everytime?

Hello Alyktre,

Would something like this work for you?

Sub Renamesheets()


Dim ws As Worksheet


For Each ws In Worksheets
    If ws.Range("W9") <> "" Then
        ws.Name = ws.Range("W9")
    End If
Next ws


End Sub

Regards
Caleeco

 

[Caleeco]

Is there a way for it to auto run without having to open VBA everytime?

Hello Alyktre,

Yes of course! If you want it to run automatically... what do you wish the trigger to be? Run the macro each time the file is opened? Everytime you change sheet selection? each time cell W9 is changes in any sheet?

Let me know
Thanks
Caleeco

 

[alyktre]

I would be looking for it to be triggered should the cell W9 be changed. If there is no change, it would stay at Sheet1 or Sheet2.

Thank you so much for your help.

Hello Alyktre,

Yes of course! If you want it to run automatically... what do you wish the trigger to be? Run the macro each time the file is opened? Everytime you change sheet selection? each time cell W9 is changes in any sheet?

Let me know
Thanks
Caleeco

 

[Joe4]

Place this code in the ThisWorkbook module in VBA (it MUST be in this module and not any other one):

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Count = 1 And Target.Address = "$W$9" And Target <> "" Then
        ActiveSheet.Name = Target.Value
    End If
End Sub

 

[Caleeco]

Hello Alyktre,

That's no problem, always happy to help! Try this out.. this code needs to be on the "This Workbook" code
> Hit ALT + F11
> in the Project window, double click "ThisWorkbook"
> In the window that pops up, paste the code below

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Authored by Caleeco
'www.ExcelWTF.com


If Not Intersect(Target, Range("W9")) Is Nothing Then
    If ActiveSheet.Range("W9") <> "" Then
        ActiveSheet.Name = ActiveSheet.Range("W9")
    End If
End If


End Sub

That should automatically update the sheet name, eveytime cell W9 is changed.

Let me know how you get on
Thanks
Caleeco!

 

[alyktre]

Could not figure out for the life of me why it would not work. I am definitely not at all skilled with Excel, other than creating masterpiece artwork with cell colors.

Anyhow, am assuming that it is not working due to the 'W9' cell is formatted to combine two other cells, G4 (store code) and B3 (customer name) to create the title of the tab. I notice this script only works if I change W9 directly, and not through its' formatting. I swear - this is my final question, is there a way to combine cells G4 and B3 to create the name through VBA instead and negate the need of the formatted cell?

Hello Alyktre,

That's no problem, always happy to help! Try this out.. this code needs to be on the "This Workbook" code
> Hit ALT + F11
> in the Project window, double click "ThisWorkbook"
> In the window that pops up, paste the code below

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Authored by Caleeco
'www.ExcelWTF.com


If Not Intersect(Target, Range("W9")) Is Nothing Then
    If ActiveSheet.Range("W9") <> "" Then
        ActiveSheet.Name = ActiveSheet.Range("W9")
    End If
End If


End Sub

That should automatically update the sheet name, eveytime cell W9 is changed.

Let me know how you get on
Thanks
Caleeco!

 

[Caleeco]

Could not figure out for the life of me why it would not work. I am definitely not at all skilled with Excel, other than creating masterpiece artwork with cell colors.

Anyhow, am assuming that it is not working due to the 'W9' cell is formatted to combine two other cells, G4 (store code) and B3 (customer name) to create the title of the tab. I notice this script only works if I change W9 directly, and not through its' formatting. I swear - this is my final question, is there a way to combine cells G4 and B3 to create the name through VBA instead and negate the need of the formatted cell?

aha! yes, the code I posted will only work if the cell is directly changed... the code below will run the macro everytime their is a cell change:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'Authored by Caleeco
'www.ExcelWTF.com
    If ActiveSheet.Range("W9") <> "" Then
        ActiveSheet.Name = ActiveSheet.Range("W9")
    End If
End Sub

although it's not advised to run code like this (it will slow down your sheet, if you have lots of other processes going on).

Yes it's easy to combine the value of cells

With ActiveSheet
    .Range("W9").Value = .Range("G4").Value & .Range("B3").Value
End With

do you wish to trigger the macro when either cell G4 or B3 are changed instead? or are they also formula driven?

Caleeco

 

[alyktre]

If it is possible to change the macro to change the tab whenever B3 and/or G4 are changed that would be amazing. They are not formatted.

aha! yes, the code I posted will only work if the cell is directly changed... the code below will run the macro everytime their is a cell change:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'Authored by Caleeco
'www.ExcelWTF.com
    If ActiveSheet.Range("W9") <> "" Then
        ActiveSheet.Name = ActiveSheet.Range("W9")
    End If
End Sub

although it's not advised to run code like this (it will slow down your sheet, if you have lots of other processes going on).

Yes it's easy to combine the value of cells

With ActiveSheet
    .Range("W9").Value = .Range("G4").Value & .Range("B3").Value
End With

do you wish to trigger the macro when either cell G4 or B3 are changed instead? or are they also formula driven?

Caleeco