Code to create copies of a 'template' sheet

I have two sheets that are to act as templates for new ones to be made... one called 'TemplateDataEntryJunior' and the other called 'TemplateDataEntrySenior'.

I have a userform called 'UsrFrmIndex', and a series of ComboBoxes and a CommandButton.

On pressing the CommandButton called 'EnterData', I'd like to have the User Form close (hide) and a particular Worksheet open.

The name of the worksheet won't always be the same one, but it will bear the name that is the concatenation (with no gaps) of the text displayed within each of the following ComboBoxes .. ComboBox 1, ComboBox 2, ComboBox 4, ComboBox 3 ... in that order.

So, for example, if the ComboBoxes bear the following texts ... '2010', 'Year8', 'Term1', 'Unit1' .... then the worksheet to be opened should be the one called '2010Year8Term1Unit1'.

Now, it's quite likely that this might be the first time a user has chosen this combination from the ComboBoxes, and if that is the case, the worksheet will need to be created from one of the two template worksheets mentioned earlier.

If ComboBox 2 displayes either 'Year8' or 'Year9' or 'Year10', then the new sheet should be created from the template sheet called 'TemplateDataEntryJunior', otherwise, it should be made from the other one .. 'TemplateDataEntrySenior'.

Once the sheet has been created (or if it had already existed, and the code would have to somehow determine if that was the case), the sheet should be opened and displayed and I'd like a particular cell to appear at the top of the screen. In the worksheet just opened, there will be some class codes in column C. I'd like the text that appears in cell 'Y3' of a sheet called 'DropBoxLists' to be looked for in column C of the worksheet just opened, and the first occurence of it to appear at the top of the screen.

What I have so far is ....

Please Login or Register  to view this content.

However, it still doesn't address the following ...
1) the 'IF' and 'ELSE' statements needed to choose whether to copy the 'TemplateDataEntryJunior' sheet or the 'TemplateDataEntrySenior' sheet.
2) how to force the first occurence of the text in column C that matches the text in 'DropBoxLists'!Y3 to be at the top of the page

Thanks for the quick response.

I'm getting an error message on the folllowing part of the code ...

Please Login or Register  to view this content.

My VBA skills are not good enough to see any errors, as it looks fine to me. I thought it might have been the bracket after the word 'False', but on removing that, I received a compile error on the first line ... 'Private Sub EnterData_Click()'.

Any ideas ?

Thanks for offering to look at it.

Unfortunately, the workbook is too large for the transfer, so I'll have to make a copy and reduce it to just the sheets necessary for you to look at.

I'll get right on it.

Thanks again,

Chris

Hi again.

Unfortunately, even with all the irrelevant sheets stripped out of the workbook, it is still too large for a transfer, unless I were to email it directly to you, but that is probably against the rules.

I removed the section of code that seems to be problematic, and when I press the 'EnterData' commandbutton, it seems to add a new sheet with the correct name no problem, but of course, it isn't sending the matching cell to the top of the screen (because that part of the code had just been removed, of course), but I also noticed, that if I chose the same combination of 4 drop boxes as had just been used to make the new sheet, it's supposed to recognise that the sheet is already in existence and therefore just take me straight there, but it didn't. It went nowhere.

What to do?

All the best,

Chris