Macro to create multiple menu sheet

[Harry Basra]

Hi

I'm trying to automate my food menu creation process. I'm trying to create a macro which will create a menu for each day based on the data in "Menu Data"

I've incluced an example of the format in the "Monday" tab, but I'm having trouble replicating this when writing the macro.

Any help will be hugely appreciated.

[Bernie Deitrick]

Click the button....

[Harry Basra]

Berine!, thank you very much for this!! This is great!

[Harry Basra]

Hi Bernie,

Please do you mind telling me how the macro is pulling the info based on your code, just in case i need to add any more data/food courses to the menu?

Please Login or Register  to view this content.

[Bernie Deitrick]

The macro assumes that your data is in groups of two: soup, then entree, and just uses math to step through the data. If you were to add, say appetizers and desserts (groups of four) then you would need to change the 2s to 4: if you added extra soups and entrees, then you would need to change the 2s to whatever the total number of items each day has.

But, and this is an important but, you would need to move the first data point to the nth row (it just worked out nicely that you have two items, and the first item is on the 2nd row). So if you have 10 items, the first item should be on the tenth row so that the indexing works out OK. ((You could also subtract an offset, but I like the simplicity of starting the data in the correct place.)

Here is an example with two soups and two specials (note that I had to fix a slight error in formatting/logic with a new version at noon EST US)

[Harry Basra]

Hi Bernie,

Sorry to trouble you again, I've spend the last day trying to get this work and just cant figure it out. I've now included the all the menu items and added all the courses.

I tried to apply your logic, but there was always something missing when i ran the macro.

Please do you mind having a look for the last time?

[Bernie Deitrick]

I took some liberties with adding prices.....

[Harry Basra]

you're great! thank you Bernie!

[Harry Basra]

Hi Bernie,

I hope you're well? I have one last questions with the sheet you worked on, please? I need to spit the soup course out as you have with the vegetable selection. So 2 lines for soup courses.

Is this something you could help with, please? I used your theory and moved it all down to the 9th line, but still noluck.

Many thanks,

[Harry Basra]

Hi Bernie,

I hope you're well? I have one last questions with the sheet you worked on, please? I need to spit the soup course out as you have with the vegetable selection. So 2 lines for soup courses.

Is this something you could help with, please? I used your theory and moved it all down to the 9th line, but still noluck.

Many thanks,

[Bernie Deitrick]

I have changed the code to allow you more flexibility without having to worry about placement or the code. As long as you have the same keywords in B as on your template, with enough rows (2 + the number of that type) then the code should continue to work without my stepping back in. Just make sure that you have the same cell merging that you currently use.

[Harry Basra]

thank you very much