Rename tab based on two cell values

[VbaHell]

Hello all

can I have some help please on this with some VBA code

i want to rename multiple tabs based on the cell value in each tab

cell values are D8, G15

result would be D8 - G15

is this possible please

 

[farmerscott]

Put this in the worksheet module you wan the code to run on...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws As Worksheet
Dim y As String

Set ws = Sheets(1) 'change sheet number to suit.

ws.Name = ws.Range("A1").Value 'change range to suit.

y = ws.Name

MsgBox "the worksheet name is now " & y

End Sub

FS

 

[footoo]

Or :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim y$: y = [A1] 'change range to suit.
If Me.Name = y Or y = "" Then Exit Sub
On Error Resume Next
Me.Name = y
If Err.Number > 0 Then
    MsgBox y & " is not a valid sheet name."
Else: MsgBox "The worksheet name is now " & y
End If
End Sub

Or :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim y As Range: Set y = [A1] 'change range to suit.
If Not Intersect(Target, y) Is Nothing And y <> "" Then
On Error Resume Next
    Me.Name = y
    If Err.Number > 0 Then
        MsgBox y & " is not a valid sheet name."
    Else: MsgBox "The worksheet name is now " & y
    End If
End If
End Sub

 

[Bokif]

Hello All,

I am new to the forum and would like to ask you for your help as I tried to solve this and I just cannot find a solution being extremely new to VBA coding and all.
Namely, I needed exactly the code for renaming sheets shared by footoo, thank you for that mate!!!
But what I am trying to do is move to the next command to turn data into tables and create connections for PowerQuery appended table.
I have managed to combine the second and third, but I cannot implement renaming sheets as a first step as it loops inside the code and won't move to the next command after all sheets have been renamed.
I tried to find a way how to write a condition after which it would move to the next command, but I am still a newbie to VBA coding and am not able to do it.

Can anybody help me?

Thank you so much in advance!
Cheers!

 

[footoo]

Post the code you are using.

 

[Bokif]

Hi Footoo!

Thank you so much for replying!

I am using the following code:

Sub RenameSheets()
Dim ws As Worksheet, x%, y%
x = 1
For y = 1 To Worksheets.Count
    For Each ws In Worksheets
        ws.Name = ws.[A2] & " - " & ws.[C2]
        x = x + 1
      
        
    Next
Next

    Sheets(1).Select
    
    For i = 1 To Sheets.Count
    Sheets(i).Activate
    
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    
    If ActiveSheet.ListObjects.Count < 1 Then
    ActiveSheet.ListObjects.Add.Name = ActiveSheet.Name
    End If
    
    Next i
    

Dim wb As Workbook
Dim ws As Worksheet
Dim lo As ListObject
Dim sName As String
Dim sFormula As String
Dim wq As WorkbookQuery
Dim bExists As Boolean

      Set wb = ActiveWorkbook
    
    'Loop sheets and tables
    For Each ws In ActiveWorkbook.Worksheets
      For Each lo In ws.ListObjects
        
        sName = lo.Name
        sFormula = "Excel.CurrentWorkbook(){[Name=""" & sName & """]}[Content]"
        
        'Check if query exists
        bExists = False
        For Each wq In wb.Queries
          If InStr(1, wq.Formula, sFormula) > 0 Then
            bExists = True
          End If
        Next wq
        
        'Add query if it does not exist
        If bExists = False Then
        
          'Add query
          wb.Queries.Add Name:=sName, _
                         Formula:="let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""" & sName & """]}[Content]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
          'Add connection
          wb.Connections.Add2 Name:="Query - " & sName, _
                              Description:="Connection to the '" & sName & "' query in the workbook.", _
                              ConnectionString:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & sName & ";Extended Properties=""""", _
                              CommandText:="SELECT * FROM [" & sName & "]", _
                              lCmdtype:=2, _
                              CreateModelConnection:=False, _
                              ImportRelationships:=False
                              
          
          End If
          
          'Count connections
          i = i + 1
          
      Next lo
    Next ws
  
End Sub

Now the part after the code that is renaming the sheets, is working well, and I get to turn all the data in all the sheets into tables, and after that, for all the tables the connection is created so I can append the data via power query.
The problem is that the first part of the code with renaming the sheets is looping indefinitely, and after all the sheets are renamed it still loops through the sheets.

I didn't find a way to make a condition for this code to complete and move to the next command.

Is there a way to do this?


Thank you so much for your help!
Cheers!

 

[footoo]

The first bit of the code you posted (to name the sheets) is :

Dim ws As Worksheet, x%, y%
x = 1
For y = 1 To Worksheets.Count
    For Each ws In Worksheets
        ws.Name = ws.[A2] & " - " & ws.[C2]
        x = x + 1
    Next
Next

Try this instead :

Dim ws As Worksheet
For Each ws In Worksheets
    ws.Name = ws.[A2] & " - " & ws.[C2]
Next

Was there anything else not working?

 

[Bokif]

Hi Footoo!

Thank you so much for sending the workaround for this!
It is working perfectly now!!!
All steps are done one after another smoothly!

I do have one general question, as I can see that you are a master of the VBA - do you have any recommendation on which online course I should take, as I would really like to be able to do, at least basic things in VBA?

Thank you so much once again!
Cheers!

 

[footoo]

There are many free Excel VBA tutorials available on the internet.
I haven't done a review of what's available.

A downloadable book you might find useful is "Macros Made Easy" available here : Macros Made Easy for Microsoft Excel
But it costs US$19.95.