VBA Code to work on multiple sheets in a workbook

**Ironman** · 09-11-2012, 08:04 PM

Hello - -

I have some VBA code that restricts data input based on the Column Number. The only column that is allowed data entry is column 7, 12 and 16. This works fine. I have another bit of code that takes this same worksheet and creates many worksheets based on values in column AT. (which came from an Excel Forum user today! Works reallly really well). That too works fine. However, what does not happen in that second vba code is the transfer of the first bit of code that blocks the data input to the newly created worksheets.

So, is there a way I can have the first code be applied to an entire workbook...so that these new worksheets have there columns blocked as well. Basically what I do end up with are 90 worksheets.

Here is the first code that blocks columns from editing:

Please Login or Register  to view this content.

Here is the second part I run to split the one worksheet to many:

Please Login or Register  to view this content.

Could I run the second one first, then apply the first vba code to the entire book? Any thoughts/suggestions would be great!

Also, food for thought another final step is for me to split out each of the 90 worksheets into their own workbook in one folder, I have code that does that, again works really well. I want to make sure that when these 90 workBOOKS are in place that the first vba code above is transfered/copied in each workbook. Again...thanks for all that take a look at this.

**JBeaucaire** · 09-11-2012, 09:04 PM

The trick is to create a Workbook_SheetChange event macro and store it in the ThisWorkbook module. This one macro watches for changes on ALL sheets and "does its thing".

I would expect that there are only specific sheets you want this macro to work on. So let's use an example of only doing "its thing" on sheets that have the word "cat" in B3.

Please Login or Register  to view this content.

I would be remiss if I didn't point out you could simply unlock the cells you want to allow editing in, then LOCK the sheet, making all the other cells unselectable. No VBA would be needed in that case.

**Ironman** · 09-12-2012, 01:33 PM

Thanks jbeaucaire - I thought I issued a response earlier but I don't see it so maybe I timed out or something. Anyway with regard to your question about protecting the worksheet: The one main worksheet in where I'd place this code will split out into 90 worksheets. These then get split out into their own workbook, so 90 workbooks in a network folder. These are then updated (in columns that allow the editing). In the past these were protected via non-vba, but the end user was not able to sort or filter or otherwise 'play' with the data. So what ended up happening was the end user would request an unprotected sheet which created various version and columns and mass confusion! So using VBA is my solution, they are still able to sort and filter but not change cell values except for those columns in the code.

With regard to my final step of creating the 90 workbooks, will this code go along with the creation of the 90 workbooks?

**JBeaucaire** · 09-12-2012, 06:35 PM

This macro is built into ONE workbook and works on all the sheets in that one workbook. If you copy the workbook as a whole, then filter that copy down to a single set of data, then you would have duplicated the macro into the new workbook. But then again, you could use that approach to use your original macro that was stored in a single sheet.

Going back to your original method, instead of using the Sheets.Add method which creates a new blank sheet with no characteristics, why not use use the Sheets("Template").Copy method, then name the copied sheet and clear all the data below row 10, then copy in your data. If your "Template" has code in the sheet module, the newly created "copies" would, too.