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


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

    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.

    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


  • 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!