Use VBA to apply Data Validation dependent on row number to new rows in table.

%3CLINGO-SUB%20id%3D%22lingo-sub-1279313%22%20slang%3D%22en-US%22%3EUse%20VBA%20to%20apply%20Data%20Validation%20dependent%20on%20row%20number%20to%20new%20rows%20in%20table.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1279313%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20that%20I%20use%20to%20determine%20item%20prices%20for%20my%20various%20menu%20items.%20When%20I%20need%20to%20enter%20in%20a%20new%20item%2C%20there%20are%20a%20lot%20of%20redundancies.%20I%20have%20a%20template%20worksheet%20that%20needs%20to%20be%20copied%2C%20the%20sheet%20needs%20to%20be%20renamed%20according%20to%20the%20item%20number%2C%20the%20tables%20in%20the%20worksheet%20need%20to%20be%20renamed%20according%20to%20the%20item%20number%2C%20a%20new%20row%20needs%20to%20be%20created%20in%20my%20menu%2C%20item%20number%20references%20within%20the%20formulas%20in%20the%20row%20need%20to%20be%20changed%20to%20match%20the%20current%20row%2Fitem%20number%2C%20and%20a%20data%20validation%20list%20needs%20to%20generated%20for%20one%20of%20the%20cells%20in%20the%20row.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20could%20automate%20all%20of%20this%20with%20VBA%2C%20that%20would%20be%20great!%20Although%20for%20now%20I%20just%20want%20to%20automate%20the%20addition%20of%20the%20new%20row%20in%20the%20menu%2C%20the%20creation%20of%20a%20new%20item%20number%20(Previous%20item%20number%20%2B1)%2C%20and%20generation%20of%20the%20new%20data%20validation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20my%20data%20validation%20formula%20that%20is%20applied%20to%20the%20%22Unit%22%20column%20(column%20C)%20of%20the%20%22Menu%22%20worksheet%3A%3C%2FP%3E%3CP%3E%3Dindirect(%22Pricing_xxxx%5BUnit%5D%22)%3C%2FP%3E%3CP%3EWhere%20%22xxxx%22%20is%20the%20item%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20my%20workbook%20for%20reference%20if%20needed.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1279313%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

I have a workbook that I use to determine item prices for my various menu items. When I need to enter in a new item, there are a lot of redundancies. I have a template worksheet that needs to be copied, the sheet needs to be renamed according to the item number, the tables in the worksheet need to be renamed according to the item number, a new row needs to be created in my menu, item number references within the formulas in the row need to be changed to match the current row/item number, and a data validation list needs to generated for one of the cells in the row. 

 

If I could automate all of this with VBA, that would be great! Although for now I just want to automate the addition of the new row in the menu, the creation of a new item number (Previous item number +1), and generation of the new data validation.

 

This is my data validation formula that is applied to the "Unit" column (column C) of the "Menu" worksheet:

=indirect("Pricing_xxxx[Unit]")

Where "xxxx" is the item number.

 

I have attached my workbook for reference if needed. 

 

Thanks for your help! 

0 Replies