CODEWITHSUNDEEP
excelrenamespreadsheetvba
Miles
Miles

Reputation: 67

Active sheet name change in sequence

I've got a workbook with two spreadsheets named "WT-1" and "CL-1" (it could be more of them with diff. names).
When i.e. "WT-1" is active, I would like to be able to (by using a button with macro assigned to it) copy this current (active) spreadsheet and rename it in sequence like WT-2, WT-3, WT-4 etc .

I guess change needs to apply only to spreadsheets who's name contains "WT-" as the name change should be addressed to the new sheet only. All other existing worksheets should not be touched. here it is - Pls help :) It changes name of one new spreadsheet. If there is more than just 1 worksheet in my workbook, it doesn't work.

Sub changeWSname()

Dim ws As Worksheet
Dim shtName As Variant
Dim Rng As Range
Dim i As Long

With Sheets("wslist")
        Set Rng = .Range("A1", .Range("A" & .Rows.Count).End(xlUp).Address)
        shtName = Application.Transpose(Rng)
        i = LBound(shtName)
End With

For Each ws In ActiveWorkbook.Worksheets
If Left(Trim(ws.Name), 3) = "WT-" Then
        ws.Name = shtName(i)
        i = i + 1
      End If
Next ws
End Sub

Macro is suppose just to change the name of a new and freshly copied spreadsheet. So if I copy WT-2 and create new sheet named WT-2(2) and run macro - it will work and change new sheet name to WT-1 (being first name in the range on 'wslist') . That seems to be OK. But, if I have any other spreadsheet in my workbook (except active sheet and already copied new sheet) it doesn't work and gives me an error 1004 - "Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic" When I click on de-bag, this I found highlighted: ws.Name = shtName(i)

Upvotes: 1

Views: 575

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

The issue is if you have the situation with following sheets

  • WT-1
  • WT-1 (2)
  • WT-2

Your code tries to rename WT-1 (2) into WT-2 but that already exists.

So a possibility was you would need to rename these to something else first like

  • WT-1
  • #WT-2
  • #WT-3

and then remove the # in another loop.

This way you prevent renaming into a name that already exists.

Option Explicit

Public Sub changeWSname()
    Dim ws As Worksheet
    Dim shtName As Variant
    Dim Rng As Range
    Dim i As Long

    With Sheets("wslist")
        Set Rng = .Range("A1", .Range("A" & .Rows.Count).End(xlUp).Address)
        shtName = Application.Transpose(Rng)
        i = LBound(shtName)
    End With
    For Each ws In ActiveWorkbook.Worksheets
        If Left$(Trim(ws.Name), 3) = "WT-" Then
            'test if we run out of sheet names
            If i > UBound(shtName) Then
                MsgBox "Running out of sheet names … aborting"
                Exit Sub
            End If

            ws.Name = "#" & shtName(i) 'add a # to all new sheet names
            i = i + 1
        End If
    Next ws

    'remove the # from the sheet nam
    For Each ws In ActiveWorkbook.Worksheets
        If Left$(Trim(ws.Name), 1) = "#" Then
            ws.Name = Right$(ws.Name, Len(ws.Name) - 1)
        End If
    Next ws
End Sub

As QHarr pointed out it's probably a good idea to test if you are running out of sheet names.

Upvotes: 3

Related Questions