Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

rename sheets using array for specific sheets

0

hi

I need design macro  to rename specific sheets . so I have  sheets (result,result1,result2)  should  rename  to (ss,mm,nn) .so  if  it's possible  do  that  without  depends on cells it will be  a great .

this  is  what  I  have  so far 

Sub ll()
Dim Oldname
Dim NewName As String
Dim sht
Dim i As Long
Oldname = Array("result", "result1", "result2")
For i = LBound(Oldname) To UBound(Oldname)
    NewName = Left(Oldname(i), Len(Oldname(i)) - 3) & Array("ss", "mm", "nn")
    For Each sht In Sheets
        If sht.Name = Oldname(i) Then
            Sheets(Oldname(i)).Name = NewName
        End If
    Next
Next i

End Sub

any  suggestion  to  fix  it ,please ?

excel
posted by: Halk (rep: 30) on Sat Sep 25, 2021 at 9:41 am
Answer
Discuss

Answers

0
Selected Answer

Halk

The modified code below will work (provided you have the two arrays of equal size, and put corresponding names in the appropriate postions). Note the changes in bold (including a new array variable, ArrNew:

Sub ll()
Dim Oldname As Variant, ArrNew As Variant
Dim sht As Worksheet
Dim i As Long
Oldname = Array("result", "result1", "result2")
ArrNew = Array("ss", "mm", "nn")

For i = LBound(Oldname) To UBound(Oldname)

    For Each sht In Sheets
        If sht.Name = Oldname(i) Then
            sht.Name = ArrNew(i)
        End If
    Next
Next i

End Sub
posted by: John_Ru (rep: 6142) on Sat Sep 25, 2021 at 11:54 am
Discuss

Discussion

that's great !  thanks  so  much
Halk (rep: 30) Sep 25, '21 at 12:20 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login