Copy sheet and rename

    Hi All


    Can anyone provide help on copying a sheet programmatically, check the last sheet number, then rename automatically with an increment of 1


    i.e.


    First Sheet is named "Summary Sheet"
    Second Sheet is named "Group 1"
    Third Sheet is named "Group 2"
    etc


    I would like to press a command button which will copy a master hidden sheet in the workbook named "MasterGroup", lookup the last sheet name in the workgroup "Group 2", then paste the worksheet and rename automatically with an increment of 1.
    i.e. "Group 3"


    I very much appreciate any assistance

    A long winded approach.

    Code
    Sub Copy_Hidden_Sheet()
Dim a As String, ws As Worksheet
a = Split(Sheets(Sheets.Count).Name, " ")(1)
Set ws = Sheets("MasterGroup")
ws.Copy After:=Sheets(Sheets.Count)
With Worksheets(Worksheets.Count)
    .Visible = True
    .Name = "Group " & a * 1 + 1
End With
End Sub

    Hi and thanks for the response


    When I use the code provided, I get an error "Subscript out of range" on line 3
    a = Split(Sheets(Sheets.Count).Name, " ")(1)


    any thoughts please


    Thanks

    If the location of the hidden sheet is not important, which apparently it is not, add a line.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login