Copy values to template & creating new sheet

**riceordie** · 10-27-2007, 08:25 PM

Hello,

I am trying to write a Macro or VBA code that will allow me to take data from a main page, copy it to a template, create a worksheet based on the completed template, and continue this process based on the amount of data listed on the main page.

Basically the sheet contains the following data:
Date
Employee Name
Register Number
Area
Over/Short Amount

This information will then get transferred to the template, which is a Disciplinary Warning for an employee. Upon the template being completed, a worksheet will then populate & be renamed to the employees name.

I have attached the spreadsheet that I am using.

Any help would be greatly appreciated!

**riceordie** · 10-27-2007, 08:30 PM

forgot to add the worksheets

**royUK** · 10-28-2007, 02:55 AM

It shouldn't be difficut, but an example workbook would be better than the .txt files.

**riceordie** · 10-28-2007, 03:19 AM

Thank you so much for you help with this. I greatly appreciate it. I was able to copy & rename one tab based on one line but I need to do this for many given lines. I would also like the Tabs to be named based on the employee name. If a given employee has more than 1 tab with his/her name, it should still name the tab based on the employee name but include a number(ie: employee#). Does that make sense? Thanks again!

**royUK** · 10-28-2007, 01:53 PM

I have added formulas to the template to populate data from the main sheet based on the employee name which is input via the macro. The code also plases the concession etc. The sheet is reproduced & named with the employee's name a the date from main formatted as mmdd.

**riceordie** · 10-28-2007, 02:27 PM

Thanks so much for your help with this...

Just one more thing:

How would I add code that would detect a duplicate tab name? I opened up the file you gave me, added information to the main page and it would not allow me to create a new tab due to a duplicate tab name error (runtime error 1004).

I don't understand why this would be happening since you added the date inclusion for the tab names, unless a person shows up twice on a given day.

Thanks again for your help.

**riceordie** · 10-28-2007, 02:59 PM

Hi, I have a couple more questions after tinkering with the sheet:

1. How do I get the newly created tabs have the same formatting as the template? All on one page? I have put together some code (after looking at other topics) that will print all of the newly created tabs but they do to have the same formatting.

2. Can I possibly get the created tabs to come after the Main & Template tabs?

3. Code to delete all tabs except for Main & Template so that I can start over if needed.

I really appreciate your help with this! I'm learning so much about VBA. Thanks!

**royUK** · 10-29-2007, 03:11 PM

The problems with copying are caused by your template design. It contains Merged Cells which generally cause problems and also some cells contain more than Excel's limit on characters. here's a different approach. Also added code to delete the sheets.
There is also a function to check whether a sheet exists and code that uses that function