Creating a macro to renaming a specific array of sheets using same cell reference in each

[rfmckay]

Hi there, I am very raw to VBA and struggling to get my head round it. Nonetheless I am trying! Your generous help and wisdom would be greatly appreciated please. As per above, I'm creating a macro to renaming a specific array of sheets using same cell reference (E1) in each. The work book needs to exclude certain sheets (in this example I've created a simple array excluding only Sheet1) and start at a specific sheet. In my working spreadhseet this would be Sheet17.Name through to Sheet66.Name.

My code is pasted below and a clever set of eyes on this would be fantastic please:

Sub RenamingSheetsRMcK()

Dim rs As Worksheet
Dim i As Long

vWSArray = Array(Sheet2.Name, Sheet3.Name, Sheet4.Name, Sheet5.Name)

For Each rs In vWSArray
If rs.Range("E1") = "" Then
i = i + 1
rs.Name = "Not used " & i
Else
rs.Name = rs.Range("E1").Text
End If
Next rs
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
What is wrong with the code you have posted?

 

[rfmckay]

Hi & welcome to MrExcel.
What is wrong with the code you have posted?

Hi there, apologies. I get an error saying Object Required.

Book4.xlsm
	A	B	C	D	E	F	G	H
1					red
2
3
4
5
6
7
8
9
10
11
12
13
MAIN

Book4.xlsm
	A	B	C	D	E	F	G	H	I
1					white
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sheet2

 

[Domenic]

First, declare rs as a Variant...

    Dim rs As Variant

Then, assuming that you are in fact referring to your worksheets by code name, replace...

vWSArray = Array(Sheet2.Name, Sheet3.Name, Sheet4.Name, Sheet5.Name)

with

    vWSArray = Array(Sheet2, Sheet3, Sheet4, Sheet5)

Hope this helps!

 

[Domenic]

In my working spreadhseet this would be Sheet17.Name through to Sheet66.Name.

The following macro will loop through each worksheet within the workbook running the code, look at the code name for the worksheet, and then look at the number portion. And, if the number is between 17 and 66, inclusive, will rename the worksheet accordingly. Note, though, you'll get an error when re-naming a worksheet with a name that already exists.

Sub RenamingSheetsRMcK()

    Dim ws As Worksheet
    Dim tmp As String
    Dim i As Long
   
    i = 0
    For Each ws In ThisWorkbook.Worksheets
        tmp = Mid(ws.CodeName, 6)
        If IsNumeric(tmp) Then
            tmp = CLng(tmp)
            If tmp >= 17 And tmp <= 66 Then
                If Len(ws.Range("E1")) > 0 Then
                    ws.Name = ws.Range("E1").Value
                Else
                    i = i + 1
                    ws.Name = "Not used " & i
                End If
            End If
        End If
    Next ws
   
End Sub

Hope this helps!

 

[rfmckay]

HI folks, many thanks to you both for your advice, very gratefully received. I can run the macro successfully to a point. The most important thing is it will rename all the sheets I need it to but when it gets to the redundant sheets it names the first one "Not Used 1" and then stops. I'm wondering if I've missed something obvious? I did some housekeeping on my field/tab names just in case but I can't think what else is wrong.

 

[Domenic]

Have you made any changes at all? If so, can you post the exact code that you're using?

Also, for the second redundant sheet (and others), is cell E1 in fact empty? Maybe it contains invisible characters? What does the following formula return?

=LEN(E1)

 

[Marc L]

but when it gets to the redundant sheets

Hi, according to your original code :​

Sub Demo1()
       Const C = "Not used ", F = "ISREF('", G = "'!A1)"
         Dim Ws As Worksheet, L&, S$
    For Each Ws In Array(Sheet2, Sheet3, Sheet4, Sheet5)
        If IsEmpty(Ws.[E1]) Then
            Do:   L = L + 1:  Loop While Evaluate(F & C & L & G)
            Ws.Name = C & L
        Else
            S = Left$(Ws.[E1].Text, 31)
            If Evaluate(F & S & G) = False Then Ws.Name = S
        End If
    Next
End Sub