Copy Template and Move Data

[elsone31]

Hi All,

I am using Excel 2007 and 2010.

I need help.

What I would like to happen:

1. Copy the Template to a new sheet in the workbook
2. Have a user input box pop up
3. Input the row number
4. The purpose of the row number is to have the macro cut the data into arrays based on like data(will be numeric and alpha)

Example: Row 1 has 4 different numbers, 109, 1110, 111, 112 so if I put the number 1 in the user box, I would get 4 tabs each one with the data array for each number.
The array for 109 would be E87:P87 and would be copied into the template. I would like the formatting to be retained.
OR if I use Row 2 it would use the names in that row to make the tabs and copy the data array.

5. The data in rows 1- 8 would also need to be copied.

Thanks in advance for any help on this.

E

[p45cal]

The attached does not use the template sheet at all, instead it copies the Data sheet and deletes columns.
This is easier to code for, and it might even be preferable to you not to have lots of empty columns in the new sheets.
There's a button in the vicinity of cell C1 of the Data sheet to run the code.
If you run the code more than once using the same user-entered row number without deleting previously created sheets, it will halt (no alert) leaving one un-renamed sheet when it tried to give the same name as an existing sheet to a new sheet and failed. The new sheet will be named something like Data (n).
Be aware that it uses row 9 and looks for the first 1 in it which it currently finds in column BK and uses that to determine how far to look for values in rows 1, 2 and 3. There are lots of ways of doing this which in your real application might be better, however, in this case be aware if there's a 1 (all by itself) in an earlier column in row 9 then it will not find all the information. Also row 9 should be the row that contains those headers, of course.
If it fails to find a 1 in row 9, it will default to column 62 (BJ) for how far to look.
With this code, you don't need the sheet Template so it can be deleted.