copy master sheet one time and rename from a list of cells

    Hi,


    I am quite new to VBA. I have a workbook that gets a sheet added once a week. I am having to do it somewhat manually, because I can not figure out how to get them to rename properly. The first sheet is "Info". It contains information that is used on all the sheets. The next sheet is "Template". It is the sheet I want to copy. After that are the sheets being added. They are named "Week1_1", "Week1_2", and so on. The first number in the name is the present quarter and the next number is the week within the quarter. So after a few weeks it needs to change to "Week2_1".


    This is what I want the code to do. I have not came up with anything that even is close enough to give as a starting point.
    1. Copy sheet "Template" to end position in workbook.
    2. Check list for last used sheet name, and rename the newly copied sheet with the next sheet name.
    The list is in "Info", A50:A88.


    Thanks, Craig

    Re: copy master sheet one time and rename from a list of cells


    Try this, it assumes that there is no data below A88 in the information sheet and that a previous used name exists in the A column.


    It will put the new name below the existing last name used in column A and will copy the template sheet and rename it.


    This is all based on just adding 1 to the last number in the last sheet name.


    So if the last sheet name used was Week1_3, the new sheet will be named Week1_4.


    Code
    Sub AddSheet()
Dim lastName As String, newName As String
lastName = Worksheets("Info").Cells(Rows.Count, "A").End(xlUp)
newName = Left(lastName, Len(lastName) - 1) & Right(lastName, 1) + 1
Worksheets("Info").Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = newName
Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = newName
End Sub

    Bruce :cool:

Participate now!

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

Register Yourself Login