Rename select tabs from cell references

djagxk8 · Sep 29th 2015

Good afternoon,

I am totally stuck on this one. I have a document with 9 tabs in it, and I only want to rename tabs 4, 5, 6, and 7 from cell E3 in each of those tabs. Everything I try either renames all the tabs, or only tab 1. I greatly appreciate any help here!

Thanks,
DT

**Trebor76** · Sep 30th 2015

Re: Rename select tabs from cell references

Hi djagxk8,

Try this:

Code

Option Explicit
Sub Macro1()


    Dim varMySheet As Variant
    
    Application.ScreenUpdating = False
    
    For Each varMySheet In Array("Sheet3", "Sheet4", "Sheet5") 'Sheet names to be included in the following procedure. Change to suit.
        On Error Resume Next
            Sheets(CStr(varMySheet)).Name = Sheets(CStr(varMySheet)).Range("E3")
            If Err.Number <> 0 Then
                MsgBox "Sheet """ & varMySheet & """ could not be renamed by the suggested name in cell E3.", vbExclamation
                Err.Clear
            End If
        On Error GoTo 0
    Next varMySheet
    
    Application.ScreenUpdating = True


End Sub

Display More

Note I've put an error trap if the text in cell E3 cannot be used as a valid sheet name, but you will have to investigate what the issue is. This includes no entry in cell E3, the entry is greater than 31 characters, there's invalid character(s), the text in cell E3 is History, there's already a tab in the current workbook with the same name as what's in cell E3 etc.

Regards,

Robert

djagxk8 · Sep 30th 2015

Re: Rename select tabs from cell references

Thanks Robert,

For some reason it's just giving me the error message. Could it be because the cell I have it referencing for the name is populated by a formula?

Here's what I have so far:

Code

Option Explicit
Sub renameregion()
     
    Dim varMySheet As Variant
     
    Application.ScreenUpdating = False
     
    For Each varMySheet In Array("Sheet6", "Sheet7", "Sheet8", "Sheet9")
        On Error Resume Next
        Sheets(CStr(varMySheet)).Name = Sheets(CStr(varMySheet)).Range("J1")
        If Err.Number <> 0 Then
            MsgBox "Sheet """ & varMySheet & """ could not be renamed by the suggested name in cell J1.", vbExclamation
            Err.Clear
        End If
        On Error GoTo 0
    Next varMySheet
     
    Application.ScreenUpdating = True
     
End Sub

Display More

Thanks,
DT

djagxk8 · Sep 30th 2015

Re: Rename select tabs from cell references

Okay, I don't know the solution, but I think I figured out the problem I'm having.

The error is happening because my sheets aren't originally named "Sheet6", and so on. Is there a way to change the sheet names regardless of what they're original names are?

Thanks again!
DT

djagxk8 · Sep 30th 2015

Re: Rename select tabs from cell references

Solved! I didn't know you could refer to the array by codename rather than worksheet name.

Code

Option Explicit
Sub renameregion()
     
    Dim varMySheet As Variant
     
    Application.ScreenUpdating = False
       
    For Each varMySheet In Array(Sheet6.Name, Sheet7.Name, Sheet8.Name, Sheet9.Name)
        On Error Resume Next
        Sheets(CStr(varMySheet)).Name = Sheets(CStr(varMySheet)).Range("A1")
        If Err.Number <> 0 Then
            MsgBox "could not rename sheets", vbExclamation
            Err.Clear
        End If
        On Error GoTo 0
    Next varMySheet
     
    Application.ScreenUpdating = True
     
End Sub

Display More

**Trebor76** · Sep 30th 2015

Re: Rename select tabs from cell references

Hi DT,

Just seen your posts - I'm glad you got it sorted out

Regards,

Robert

Rename select tabs from cell references

Participate now!

Share