Loop

[camz100]

Hi there. I have no prior experience of VBA, and have searched around the forum for my solution, but no luck. I'm pretty sure what I'm after is simple enough for the VBA folk.

I have a spreadsheet with lists of personnel data. Column A is the column with the names of the personnel. Typically there are up to seventy people (rows), but I want to future proof this to max of 200.

I want to run a macro and have it create a worksheet for each person in Column A and rename the worksheets with the name of the person. I've managed to cobble together a basic macro which allows me to create one worksheet based on a single cell, but I have no idea how to create the loop effect. I'm very familiar with Excel formulae, and have created a COUNTA on the list of rows to give a running total of 'live' rows, if that makes the loop easier to function.

Here's my basic macro so far:

Please Login or Register  to view this content.

So that code creates a new worksheet and names it with the value in cell A1.

I intend to have the master template to have a couple of sheets prior to any population and running of macros, so not sure if those named sheets should be in the macro, or if their numerical position (Sheets(1)) is better. Ideally, I'd like to use a 'deletable' worksheet as the source of the data for the macro (a copy of the master sheet), and have the macro delete the copysheet as the last step, to prevent accidental re-running. After populated, the spreadsheet will be saved as something else to help prevent this too.

All other copy/paste data stuff within the VBA should be easy enough with the recorder.

Complete novice. Hopefully too much info is better than not enough.

[jasoncw]

Try this to see if it works for you. It will work for as many names are in column A, starting with row 1.

Please Login or Register  to view this content.

HTH,
Jason

[jasoncw]

Adding to the code above, to accomplish I believe everything you are requesting, you could try the procedure below (although I don't know why you would want to copy a sheet and then delete it).

Please Login or Register  to view this content.

HTH,
Jason

[camz100]

Hi Jason.

That first post does the trick perfectly! Will have a more deatiled look at the second one tomorrow. Late nights and programming don't mix for me etc.

The only reason to delete the sheet would be so if the macro was re-run, it would fail if it can't find the source data. Making it a 'run once' protects the data. The sheet would most likely contain a column with cells saying =Master!A1 or similar. Just so the macro can pick the raw text up, but not access it a second time and create another 70+ sheets.

[jasoncw]

Ok, well in my 2nd post above, I included a preventative measure for that. It will check to see if the sheet exists first. If it does, the sheet will not be copied. The procedure will still run and check each name, in case it was run once, names were added, and you want to run it again. If that is the sole purpose of copying the worksheet, then it is not necessary, so try this one instead:

Please Login or Register  to view this content.

Jason

[JBeaucaire]

This is my little ditty for making new sheets based on a list in column A. It will only make sheets that don't exist already.

Please Login or Register  to view this content.