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

 

[footoo]

Sub RenameSheets()
Dim ws As Worksheet, x%, y%
x = 1
For y = 1 To Worksheets.Count
    For Each ws In Worksheets
        On Error Resume Next
        ws.Name = ws.[D8] & " - " & ws.[G15]
        If Err.Number > 0 Then
            ws.Name = "Check " & x
            x = x + 1
        End If
        On Error GoTo 0
    Next
Next
End Sub

If the macro renames any of the sheets "Check" followed by a number, it will be because either there is already a sheet with the required name or there is an invalid sheetname character in D8 and/or G15.

 

[VbaHell]

Hi Footoo

Sorry for the late reply, been on holiday
Thanks for your support on this

 

[VbaHell]

This is not quite working correctly, it renames the first 6 tabs then defaults to the error "Check" even though the [G15] is different
The [D8] value is aleays the same but the [G15] is always different.

Anyone have any idea's on how to fix this please

Sub RenameSheets()
Dim ws As Worksheet, x%, y%
x = 1
For y = 1 To Worksheets.Count
For Each ws In Worksheets
On Error Resume Next
ws.Name = ws.[D43] & " - " & ws.[D5]
If Err.Number > 0 Then
ws.Name = "Check " & x
x = x + 1
End If
On Error GoTo 0
Next
Next
End Sub

 

[VbaHell]

Found the issue, there were to many digits to rename the tad - Doh!

 

[footoo]

It's working for me and I can't replicate the problem.

Post the sheet names and the D8 and G15 values for each sheet.

 

[VbaHell]

Sorry Footoo, it my mistake, the file name got to big to rename the tab, once i sorted this it now works find

Thanks for all your help on this

 

[tahanouman]

Sub RenameSheets()
Dim ws As Worksheet, x%, y%
x = 1
For y = 1 To Worksheets.Count
    For Each ws In Worksheets
        On Error Resume Next
        ws.Name = ws.[D8] & " - " & ws.[G15]
        If Err.Number > 0 Then
            ws.Name = "Check " & x
            x = x + 1
        End If
        On Error GoTo 0
    Next
Next
End Sub

If the macro renames any of the sheets "Check" followed by a number, it will be because either there is already a sheet with the required name or there is an invalid sheetname character in D8 and/or G15.

Hey, how can we apply it for only one worksheet? I applied it by selecting the sheet from objects but still, it applies to the workbook.

 

[farmerscott]

Welcome to the Board.

which cell gets the name for the sheet? Is it the same sheet?

thx

FS

 

[tahanouman]

Welcome to the Board.

which cell gets the name for the sheet? Is it the same sheet?

thx

FS

Yeah, the cell is of the same sheet to be renamed. Thanks for replying so quick!