View Full Version : [SOLVED:] Rename excel worksheets based on values contained in 1 excel tab
John Beaver
08-23-2016, 12:35 PM
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
08-23-2016, 01:17 PM
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
08-23-2016, 01:30 PM
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
08-23-2016, 01:48 PM
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
08-24-2016, 03:52 AM
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
08-24-2016, 06:07 AM
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
08-24-2016, 07:09 AM
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
08-24-2016, 09:39 AM
Above your first post there's [Thread Tools] with a drop down for you to mark this thread as solved
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.