Renaming sheets in workbooks based on criteria

[pradeep.thakur]

Hi,
Im trying to create a macro to rename the sheets in a list of workbooks, if the names differ from a source list of sheet names. Here is the code

Sub RenameSheets()
    Dim w, x, y, Z As Integer
    Dim tabs, lasttab, extractedtabs, str, stra, lastrow, lastbook As Range
     Set tabs = Workbooks("Control").Sheets("Control").Range("Q7:Q31")    ' List of desired tabs
     Set lastbook = Workbooks("Control").Sheets("Control").Range("F7:F80")    ' List of files to be modified
    Set lastrow = lastbook.find("")
    Set lasttab = tabs.find("")
    w = lasttab.Row - 1
    y = lastrow.Row - 1
        For Z = 7 To y
             Set extractedtabs = Workbooks("Control").Sheets("Macros").Range(Cells(Z, "H"), Cells(Z, "AF")) ' Problem setting this up as H7:AF7 (row increments for every file); refers to existing sheets in every file (pulled by another macro)
            Application.DisplayAlerts = False
             Workbooks.Open Filename:=Workbooks("Control").Sheets("Control").Cells(Z, "I").Text   ' Path of file to be modified
                Dim sh As Worksheet
                    For Each sh In ActiveWorkbook.Worksheets
                        Set str = tabs.find(sh.Name)
                        If str Is Nothing Then
                            For x = 7 To w
                                Set stra = extractedtabs.find(Cells(x, "Q"))
                                If stra Is Nothing Then
                                     sh.Name = Workbooks("Control").Sheets("Control").Cells(x, "Q").Text
                                    Exit For
                                Else
                                End If
                            Next
                        Else
                        End If
                    Next
                Application.DisplayAlerts = True
            ActiveWorkbook.Save
            ActiveWindow.Close
        Next
End Sub

Whats it needs to do is,

• Compare the sheet names with a standard list (a table of corresponding sheets for corresponding files).
• Then, if the active sheet name is not present in the desired list, rename it as per the list.
• Order is not important as another macro will have deleted sheets from the workbook (with exceptions which will not need to be renamed as they are already appropriately named) and then added new blank sheets based on specific totals for each file.

Here is table of corresponding sheets

Green cells imply that the sheet is required
Red implies sheet is not required
Check verifies total with another source and returns difference

Please advise.
Thanks

 

[pbornemeier]

Are all of the sheets that are being renamed blank? If not, how do you decide which sheets should get which name?

Please expand on your comment:

Problem setting this up as H7:AF7 (row increments for every file); refers to existing sheets in every file (pulled by another macro)

Will either of these code shippets help with that?
You can use this code to determine the last filled row in column 1:
Range(Rows.Count,1).End(XlUp).Row
and this code to determine the last filled column in row 7:
Range(7,Columns.Count).End(xlToLeft).Column

 

[pradeep.thakur]

Are all of the sheets that are being renamed blank? If not, how do you decide which sheets should get which name?

Please expand on your comment:
Will either of these code shippets help with that?
You can use this code to determine the last filled row in column 1:
Range(Rows.Count,1).End(XlUp).Row
and this code to determine the last filled column in row 7:
Range(7,Columns.Count).End(xlToLeft).Column

Hello Phil

Firstly, I'm very thnkful for your reply.

Initially, none of the sheet names will be blank. I have just used generic names as an example.

For every file, I will explicitly retain 2-3 specifically named sheets, the rest will be deleted and blank new sheets will be inserted accordingly to match the total required sheets for each file ( all this is being done by another correctly working macro).

In case i just clear contents instead od deleting sheets, need for renaming will arise, but the problem with this approach is that with the exception of 2-3 sheets (excluded already), all other sheet names (nd the included data) will frequently change

Every file being edited here has a corresponding source file whose tab names i will extract (stored in the range referenced by "extractedtabs" in code in OP).

This range is from H7:AF7 but will move down one row for each file which is what i was trying to implement.

Thanks again for your time

 

[pradeep.thakur]

Editing typos

Hello Phil

Firstly, I'm very thankful for your reply.

Initially, none of the sheet names will be blank. I have just used generic names as an example.

For every file, I will explicitly retain 2-3 specifically named sheets, the rest will be deleted and blank new sheets will be inserted accordingly to match the total required sheets for each file ( all this is being done by another correctly working macro).

In case i just clear contents instead of deleting sheets, need for renaming will still arise, as with the exception of 2-3 sheets (excluded already), all other sheet names (and the included data) will frequently change.

Every file being edited here has a corresponding source file whose tab names i will extract (stored in the range referenced by "extractedtabs" in code in OP).

This range is from H7:AF7 but will move down one row for each file, which is what i was trying to implement.

Thanks again for your time

 

[pbornemeier]

I do not understand what is not working with your code.

What is not working? What should it do?

 

[pradeep.thakur]

I do not understand what is not working with your code.

What is not working? What should it do?

The outer for loop looks up the sheet name in the range tabs ( a column list - same for every file, sort of master sheets that each file must possess).

If it fails to find a match here, the nested for loop looks for a match in another range "extractedtabs" (a horizontal list, distinct for each file, mainly tabs in addition to above tabs).

Now if both checks fail, then the sheet must be renamed.

I think the checking part in my code is ok, just stuck at the renaming part.

The tricky part for me is how to utilize both the ranges to decide the name to use.

Follwing is an example of the ranges:


extractedtabs looks like this

File A	GK	DEK	MID	ATT
File B	LW	RW	CAM
File C	ST	GK
File D	CB	LB	CDM	CM

<tbody>
</tbody>

Range "tabs" like this

TABS

Italy

Germany

Spain

Portugal

<tbody>
</tbody>

Thanks for your help