+ Reply to Thread
Results 1 to 9 of 9

Copy and Rename a sheet (template) from a cell value entered in the template

  1. #1
    Registered User
    Join Date
    02-20-2013
    Location
    indiana
    MS-Off Ver
    Excel 2010
    Posts
    1

    Copy and Rename a sheet (template) from a cell value entered in the template

    I am new to the forum so please excuse my form.

    I have been trying to figure out how to create a copy of my employee attendance template. Is there a way to create a copy of the template by entering an employee name in the "name" cell of the template and it automatically renames the sheet that employee name and also saves or recopies the template for use with the next employee?

    Thank you

  2. #2
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Copy and Rename a sheet (template) from a cell value entered in the template

    Yes this is very easily doable. Just attach a sample file with dummy data.

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    02-18-2013
    Location
    lafayette IN
    MS-Off Ver
    Excel 2010
    Posts
    5

    Smile Re: Copy and Rename a sheet (template) from a cell value entered in the template

    Thank you for your reply! I have attached the file (I think. Did I mention I was working in Excel 2010? I left my spread sheet the way I was working with it. there are 2 sheets, one called master and one called template1. they are identtical except there may be some code in them and they dont work well. I am new to Macros and I am afraid I have made a bit of a mess with them. What i would like to see happen it that when I open the Master(orTemplate) I enter a name into the Name Cell. at that point it will rename that sheet to the name of the employee and at the same time create a new copy of the master sheet to be ready when a new employee is hired. (or it could copy and rename the copy to the employee name and insert that same name into the cell called Name.

    Thank you again for your help

  4. #4
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Copy and Rename a sheet (template) from a cell value entered in the template

    Put this code in the Master sheet code window -
    Please Login or Register  to view this content.
    Ensure that there is no code in the code windows for the other sheets. I am saying this because you had the same code in multiple sheet code windows. You just need it one sheet. This code will make a copy of the template1 sheet using the name in the Master sheet cell D5. The reason why the code is not making a copy of the master sheet, is because every new sheet will land up having the code in it once copied. Or instead of making a copy of the sheet, we can copy all the cell contents n format. This way, the code will not be duplicated.

    Another option is to ask the user to provide the username and then the sheet is copied or cell contents are copied.

  5. #5
    Registered User
    Join Date
    02-18-2013
    Location
    lafayette IN
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Copy and Rename a sheet (template) from a cell value entered in the template

    I worked on this all week end and came up with a solution, but it has some issues. Could you please take a look at it? If I cancel the pop up window I get a run time error. and a copy of the template is created. So I have multiple copies. I also created an index where the new sheets are added. But I would like to sort the index sheet in alpha but keep the sheet "list" and "template1" on top Is this possible. I have attached a copy of my file for your review.
    Thank you for your help
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Copy and Rename a sheet (template) from a cell value entered in the template

    You confused me there with 2 ids. Why are you using 2 different IDs?

    Did you try the code i posted?

  7. #7
    Registered User
    Join Date
    02-18-2013
    Location
    lafayette IN
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Copy and Rename a sheet (template) from a cell value entered in the template

    I am sorry for the confusion. I have 2 id's because I was at home this week end and was unable to access the forum using my work ID so I created another one. I have not tried your code yet because I changed my spreadsheet quite a bit since I sent you the first copy. THat is why I sent you the second one.

  8. #8
    Registered User
    Join Date
    02-18-2013
    Location
    lafayette IN
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Copy and Rename a sheet (template) from a cell value entered in the template

    I get a run time error 1004

  9. #9
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Copy and Rename a sheet (template) from a cell value entered in the template

    On which line?

    You need to follow the instructions -
    Ensure that there is no code in the code windows for the other sheets. I am saying this because you had the same code in multiple sheet code windows. You just need it one sheet. This code will make a copy of the template1 sheet using the name in the Master sheet cell D5. The reason why the code is not making a copy of the master sheet, is because every new sheet will land up having the code in it once copied. Or instead of making a copy of the sheet, we can copy all the cell contents n format. This way, the code will not be duplicated.

    Another option is to ask the user to provide the username and then the sheet is copied or cell contents are copied.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1