Rename excel worksheets based on values contained in 1 excel tab

[John Beaver]

Hello all,

Thank you very much for reading this post and taking your time to answer me.

My sole experience with VBA consists of "copy paste commands" so would very much appreciate it if you gave out little snippets of information about what each bout of programming does in your answer :).

Am looking to use a specific excel sheet, let's call it "summary" so that modifying the name on that excel sheet modifies directly the name of each excel tabs (they're already created), and vice versa, modifying the excel name of each tabs would modify the content of the excel sheet "summary".
Ex: in Excel sheet "summary" cell A5 to A35 would each contain information, say "1", "2" "3" and so on until "30".
Running the macro would enable the sheets after summary ("sheet 1" sheet 2" "sheet3" until "sheet 30") to automatically be renamed "1", "2", "3" and so on.
Likewise, renaming sheet "3" into "test sheet" would automatically change the content of cell "A7" from "3" to "test sheet".
Your help would be very much appreciated.
Thank you and regards,

Johanssen

[jolivanes]

In your attachment.
First you have "the name of each sheet etc" and later "this is the excel sheet etc"
One refers to quite a few sheets and the other refers to one sheet only.
Which of the two possibilities is it?

[John Beaver]

Hello Jolivanes,

Thank you for your reply.

I would like the summary excel sheet to refers to all the other sheets, such as the change in the name made on the summary excel sheet affect all the other excel sheets name.
Reciprocally, changing manually the excel name of the sheets should update the excel cells in the first "summary" excel tab giving out their names.

[jolivanes]

Reciprocally, changing manually the excel name of the sheets should update the excel cells in the first "summary" excel tab giving out their names.
I've never heard of this before and I don't know if it is possible.
You just need to wait for someone who knows how to do this.
Sorry about this

[Aussiebear]

Perhaps this would work, given that its meant to create a dynamic list of the sheets

Private Sub Worksheet_Activate()
Dim xSheet As Worksheet

Dim xRow As Integer

Dim calcState As Long

Dim scrUpdateState As Long

Application.ScreenUpdating = False

xRow = 1

With Me

.Columns(1).ClearContents

.Cells(1, 1) = "INDEX"

.Cells(1, 1).Name = "Index"

End With

For Each xSheet In Application.Worksheets

If xSheet.Name <> Me.Name Then

xRow = xRow + 1 names

With xSheet

.Range("A1").Name = "Start_" & xSheet.Index

.Hyperlinks.Add anchor:= .Range("A1"), Address:= "", _

SubAddress:= "Index", TextToDisplay: = "Back to Index"

End With

Me.Hyperlinks.Add anchor: = Me.Cells(xRow, 1), Address: = "", _

SubAddress: = "Start" & xSheet.Index, TextToDisplay: = xSheet.Name

End If

Next

Application.ScreenUpdating = True

End Sub

[Paul_Hossler]

Maybe this


In Thisworkbook



Option Explicit
Private Sub Workbook_Open()

FillData

wsMaster.Select
End Sub


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

FillData

rMaster.Cells(Sh.Index).Value = Sh.Name
End Sub




In WS summary



Option Explicit

'code in Sheet1(summary)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Long

FillData

If Intersect(Target.Cells(1, 1), rMaster) Is Nothing Then Exit Sub

iRow = Target.Cells(1, 1).Row - rMaster.Rows(1).Row + 1
Worksheets(iRow).Name = CStr(Target.Cells(1, 1).Value)
End Sub




In a standard module




Option ExplicitPublic aWS() As String
Public wsMaster As Worksheet
Public rMaster As Range

Sub FillData()
Dim r1 As Range, r2 As Range
Dim ws As Worksheet

Set wsMaster = Worksheets("summary")
Set r1 = wsMaster.Cells(1, 1).End(xlDown)
Set r2 = wsMaster.Cells(wsMaster.Rows.Count, 1).End(xlUp)
Set rMaster = Range(r1, r2)

ReDim aWS(1 To Worksheets.Count)

For Each ws In Worksheets
aWS(ws.Index) = ws.Name
Next

End Sub



It's not nearly as robust as it should be with error checks, etc. but it might be a good starting point

I had to add 'summary' to the list to keep things simple

[John Beaver]

Hello all, thank you very much for your input! My question has been solved and it works!
Thank you so much for all your answers, and your swift replies, I really appreciate it. :D

[Paul_Hossler]

Above your first post there's [Thread Tools] with a drop down for you to mark this thread as solved