Active sheet name to change in sequence by macro

[epic159]

Hi there,

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 .

EXAMPLE
If i.e. I have 12 spreadsheets (WT-1 to WT-12) already created in my workbook (among others) and currently active spreadsheet is let's say WT-4, when macro is triggered it should copy WT-4 into a new spreadsheet named WT-13 (first next available name that cotains 'WT-').
I don't mind if a hidden list needs to be used to succeed in solving this issue. The name change should apply only to a new spreadsheet(s) who's name contains "WT-" (active spreadsheet, and the other sheets, should not be affected). Pls help
Miles

 

[epic159]

I have tried this macro, but if there is more than just one spreadsheet in my workbook it doesn't work:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">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</code>

 

[Webbarr]

Hey, hope this helps. I've broken it out into a couple of functions which can be recycled in other code you use & a calling sub.

fnListWorksheets() will list the worksheets into a comma delimiter string, you can give it an optional parameter if you only want to search for worksheets LIKE something. e.g. WT- etc...

Public Function fnListWorksheets(Optional strWorksheetPrefix As String) As String
    Dim ws As Worksheet, strWSCSV As String
    
    For Each ws In Sheets
        If strWorksheetPrefix <> vbNullString Then
            If ws.Name Like strWorksheetPrefix & "*" Then strWSCSV = strWSCSV & ws.Name & ","
        Else
            strWSCSV = strWSCSV & ws.Name & ","
        End If
    Next ws
    
    fnListWorksheets = Left(strWSCSV, Len(strWSCSV) - 1)
End Function

fnCopySheet() will just copy the activesheet next to it, if you give it the optional strWorksheetName it will rename that sheet for you as well

Public Function fnCopySheet(Optional strWorksheetName As String) As String
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    ws.Copy , ws
    
    Set ws = ActiveSheet
    If strWorksheetName <> vbNullString Then ws.Name = strWorksheetName
End Function

This is a sample sub you could use, although i expect you may want to add some other stuff, I hope it's a good start point for you.

Sub buttonPress()
    Dim astrWSList() As String
    
    astrWSList = Split(fnListWorksheets(Left(ActiveSheet.Name, 3)), ",")
    
    fnCopySheet Left(ActiveSheet.Name, 3) & (UBound(astrWSList) + 2)
End Sub

Let me know if this helps!

 

[epic159]

Thanks a lot for your help - it is interesting and it will be used that's for sure.
I just received an interesting solution for my issue - I guess you may be happy to see it and use it. Maybe you can even let me know if you would improve anything? I have tested it and it worked well:

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
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)
i = i + 1
End If
Next ws
End Sub