Use VBA to create copies of hidden sheets based on user input with specific names

Are you asking someone to do this for you or someone to explain and give tips on how to do it? I ask so I know how to answer you.

The gist of what your asking is pretty straight forward from a logical stance. I would create my "template" sheets, trying to "reuse" as much as possible sheet to sheet. In other words start with 1 template sheet and place on it what is common among all template sheet. Then copy that template sheet x number of times to create the rest of the template sheets and then customize each with what is unique to each.

Next you will need to consider an input mechanism for the user. Depending on how much they need to enter and in what ways this could be a sheet itself, a simple input box or a form. Personally I find sheets to be the easiest to work with for many inputs. For simple inputs using input boxes works easily enough but can be bad after prompting a couple times. I reserve a form for when doing it sheet level or via input box isnt viable.

Once you collect the input from the user its just a matter of working out the logic and how you break down what was given into what needs to be delivered. So for example you may take a number they enter (say for the number of sheets) and use that as the bounds of a for loop that copies the sheet. IE: they want 10 sheets so you have a loop that is something like:

Please Login or Register  to view this content.

where intUserInput is a variable we stored their response of 10 in. The For loop executes 10 iterations of whatever code is placed in the loop, say code to copy the template sheet.

You would also then need to consider smaller aspects of the code, like unhiding the sheet and re-hiding a sheet to act upon it. How hidden does the sheet need to be, there is hidden and very hidden (seriously, its a thing). What do you do if the user picked the wrong number and whats to redo it? What do you do with the extra sheets when the user is done (delete them, keep them in the file, export them to their own file, etc)?

Regardless of if you are asking someone to do it for you or guide you to do it, the best thing you can do to help it along is really think over what you need it to do, both overall and specifically step by step. It will help you manage your ideas and better explain it to others.

So if learning VBA is important to you, and it sounds like it is, then having a pet project to help motivate you is great. Having said that, people who want to learn to drive dont do so by driving Le Mans as their first driving experience.

To do what you are asking to do requires a foundation in VBA. You need to understand the Excel object model, the syntax and the methodologies for doing common VBA tasks.

It would likely greatly benefit you to pick up a Excel book that covers both the standard stuff and VBA. I personally like John Walkenbach's books, the Excel bible, for getting a good foundation down. To automate via VBA various aspects of Excel you first have to understand how they work without coding. As an example (not saying this is you), if one doesnt understand a table in Excel...its structure, using structured references, etc. then its a stretch to expect to programatically manipulate it.

You will also need to understand general programming concepts like variables, loops, if statements, arrays to make efficient use of VBA. The other side of it is understanding the object model of Excel and the syntax of VBA in Excel.

Many people in your shoes start by creating a form as its a visual exercise and fairly intuitive. I know I used to, when I didnt know VBA or know it well. As I learned I started coding more first and leaving designing the form, if I used one at all, until later in my work. The reason being, as you write the code you will come across problems you hadnt considered before, which could alter the form. So imagine you create the form, then write code. In the course of writing the code you find 3 things you hadnt considered initially that require altering the form. You have now had to work with the form 4 times instead of 1 time.

In my previous post I gave you a bit of an overview of what you would need to do and consider. You could start researching those things if you care to. Otherwise maybe someone here is willing to do it for you and then explain it.

In addition to all the excellent advice and suggestions ZerO has given, if there's one single additional piece of advice I can give it's this.
Don't make the same mistake we see a lot, and that's having your data records in non normalised regular tables/ranges or spread around the workbook.

You mention picking up one of 5 different templates for any user input and then apparently creating separate score sheets and that worries me. I'm assuming that at some stage you will want to derive various statistics from all your users inputs so it's vital that you create a normalised regular 2 dimensional table of data where unique data fields are in the first row as column labels and each row is a unique instance of a record.

And by unique data field that does not mean that column labels would be say Round1, Round2...Roundn..etc. You would have a column label for 'Round' and each record underneath in that column would contain a reference to the round, i.e. 1, 2...n etc.

It would be useful if you could upload the templates you are planning to use and examples of the data therein so that we can offer further advice. I feel at the moment that you're starting at the wrong end with worrying about how to display the templates.

The first thing to decide IMO is how and where the data should be held and what information or statistics you might eventually want to show, (i.e. try to future proof it), and only then how best to capture that data - which may indeed be from one of 5 templates but equally there may be a more efficient way.

Richard also gives some excellent advice I hadnt offered but will second.

A) As part of understanding Excel and how it works, a fundamental yet often overlooked aspect is that it loves tabular data. If you follow the structure of data that Excel expects...the possibilities for how you can analyse and process that data open up. That is to say tabular data has headers, 1 row per record, each column is a unique field. Simple to say, for some reason hard for people to do.
B) Planning. I think the saying is, "measure twice, cut once". I generally draft out an outline of what I need to do before I even write code. Each bullet point being a aspect of what the macro should do. Sometimes I do multiple outlines; major steps the macro takes, major issues it can run into, logical flow, etc.

For example if I wanted to write a simple macro to put "Hello World!" in a cell on a sheet I may outline that like:

• Hardcode sheet or prompt user for sheet and cell?
• constant for "Hello World!"
• Range.value method to assign string to cell

And I may then do another outline for troubleshooting problems I anticipate:

• Is cell filled already? overwrite, cancel filling, store prior value elsewhere or put "Hello World!" in another location?
• If I prompted user for sheet/cell, check its valid
• Check that cell/sheet isnt locked

So as you can see even a simple macro has a fair amount of consideration to put into it. A good programmer will with experience and some planning start to consider issues before they happen. For example in the above if I prompt the user for a sheet and cell, I have to make sure they actually enter a valid sheet and cell. Also, even if they do, I have to make sure the sheet/cell are not locked (unless I am 100% sure this wont be possible).

I also add error checking to all my code. I personally feel no user should ever be presented with the debug option. You are just asking for trouble if you let them do this. Also, MS isnt known for their wonderful error dialogs. If there are common errors I know can happen, I can write a simple to understand error message and display it to the user instead of the standard debug dialog.

However it all comes down to, like Richard said, start at the beginning. Give yourself a strong foundation in Excel itself, then start with the fundamentals of VBA and exercise what you are learning in practical ways.

Lastly, part of being a good programmer (at least in VBA) is knowing when to NOT use code to do what you are doing. That knowledge comes from experience and a sound understanding of both Excel, its underpinnings and VBA. While it may sound like we are not addressing your immediate concern, we are trying to give you the tools to be successful long term.