Need a Macro to Copy template excel sheet multiple times and rename sheets based on list on hidden sheet

  • In search of a macro that will do the following

    I have a sheet1 "student" = active sheet
    sheet3 "Sort Sheet" = hidden sheet (contains a list in column C, Row 1 to 12)

    The list will not be more than 12 rows, but could be as few as 2

    I would like the user to press a "button" on the sheet that will:

    1. Copy the active sheet

    2. Rename the current active sheet to the name of a student in "Sort Sheet" column "C":Row "1"

    3. Copy the current active sheet after the active sheet

    4. Rename the new active sheet to the next name in the "Sort Sheet" Column "C":Row "2"

    5. Continue the process until all sheets have been copied and renamed based on the list

    Thank you for all your help! Cheers!

  • "Student" sheet will be the active sheet the user will view and interact with.

    The following sheets will be hidden from user:

    1. Sort Sheet

    2. 2021 - Smart Phonics 3 Grades

    3. Book Info

    4. Class_Book_List

    5. Complete_Student_List

    Numbers 2, 4, 5, will update from other files on network.

    Number 1 - "Sort Sheet" will update from drop down lists chosen by the user on the "Student" sheet.

  • Thanks. Do you need each new sheet to update with the data for that student? In other words automatically enter the student E & K Full Name in cell P24?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Try the attached. I have hidden all but the Student sheet.

    As you no doubt discovered, the new Excel SORT and FILTER functions are great, except for when trying to get the results of the functions into VBA. You need to go back to the source Table and get the required data from there.

    This is the code now assigned to the button

Participate now!

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