Excel Weekly Meal Planner

Contextures - Microsoft Excel Tips and Tutorials
Search Contextures Sites

Home
Excel Tips
Excel Files
Products
Blog
Contact
RSS
Contextures
Excel news
by email

Email:

Name (optional):

We respect your email privacy
Excel Weekly Meal Planner

Thanks to Jimmy Peña, who created the Recipe Selector tool in the meal planner.

Select Recipes
Enter the List of Meal Items
List the Meal Ingredients
Select the Weekly Meals
Create the Weekly Shopping List
Print the Shopping List
Watch the Excel Weekly Meal Planner Video
Weekly Meal Planner VBA Code
Download the Excel Weekly Meal Planner

When the kids are in school, and everyone is involved in evening activities, it can be tough to get your family meals organized.

To help you make the job easier, you can use this Excel weekly meal planner. Enter your favourite meals, list their basic ingredients, and click a button to create a shopping list for a week's meals.

Note: For special occasions, there's an Excel holiday dinner planner that you can use to organize a meal for a large group, or a fancy dinner for friends..

Select Recipes

The first step is to select recipes for your weekly meals, either from your own collection, or on the Recipes sheet in the Excel Weekly Meal Planner.

Enter an ingredient name in cell A2, then click the Get Recipes button. A macro runs, and pulls a list of recipes for the selected ingredient. To see one of the recipes, click the link.

Enter the List of Meal Items

The next step is to list your family's favourite dishes on the Meal_Items worksheet. There are a few sample items in the Excel weekly meal planner. You can add new items to the list, or replace the existing meals with your family favourites.

In column B, enter the name of the dish, and in column C you can enter a link to a recipe online, or type a cookbook name and page number.

List the Meal Ingredients

After you have entered a list of meal items, enter the main ingredients for each item on the Meal_Ingredients worksheet.

There is a data validation drop down in column B, where you can select the name of one of your meal items.

In column C, enter the quantity required, and in column D, enter the unit, such a can, cup or lb.

In column E, enter the name of the ingredient.

Finally, in column F, enter the category for the ingredient, to help you make a shopping list.

Note: There are formulas in columns G and H, which are used to create the shopping list, so don't type in those columns.

Select the Weekly Meals

After you have entered all the ingredients, you're ready to start the weekly meal planning.

On the WeekdayMeals sheet, select up to 3 items for each weekday meal, by using the data validation drop down lists in columns C:E.

Create the Weekly Shopping List

After you select each day's meals, click the Shopping List button at the top left of the WeekdayMeals sheet.

This runs a macro, which uses an advanced filter to copy all the ingredients for the selected meals to the ShoppingList sheet, and sorts the shopping list by ingredient name.

As you select each day's meal, that meal's ingredients are marked with a 1 in column G, on the Meal_Ingredients worksheet. On the Meal_Items sheet, in cells G1:G2, there is a criteria range that's used in the advanced filter. It checks for ingredients that are marked with a 1 (or higher) in column G.

Print the Shopping List

After the shopping list has been created, the items are summarized in an Excel Pivot Table. You can print the list, add any other grocery items that you need, and then head to the grocery store.

Watch the Excel Weekly Meal Planner Video

To see a quick overview of how the Excel Weekly Meal Planner works, you can watch this short video.

Weekly Meal Planner VBA Code

Shown below is the code for the weekly meal planner sample file.

The first macro -- CreateShoppingList -- goes on a regular code module.

The remaining code goes on the sheet module for the Recipes worksheet

Put this code on a regular code module. It creates the shopping list, based on the selected meals.
Sub CreateShoppingList()
Dim wsShop As Worksheet
Dim wsIngred As Worksheet
Dim wsList As Worksheet
Dim wsItems As Worksheet
Dim wsPrint As Worksheet

Set wsShop = Sheets("ShoppingList")
Set wsIngred = Sheets("Meal_Ingredients")
Set wsItems = Sheets("Meal_Items")
Set wsPrint = Sheets("ShopListPrint")

wsIngred.Columns("B:J").AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=wsItems.Range("CritShop"), _
    CopyToRange:=wsShop.Range("ExtShop"), _
    Unique:=False
    
With wsShop
    .Cells(1, 2).CurrentRegion.Sort _
        Key1:=.Range("C1"), _
        Order1:=xlAscending, _
        Header:=xlYes
End With

With wsPrint
    .PivotTables(1).PivotCache.Refresh
    .Activate
End With

End Sub
Put this code on the Recipes sheet code module (right-click the Recipes sheet tab, click View Code, and paste the code there)
Option Explicit


Private Sub CommandButton1_Click()
  Call GetRecipes
End Sub


'------------------------------


Sub GetRecipes()
  On Error GoTo ErrorHandler

  Const RangeToImport As String = "$A$10"

  ' delete existing xml map (if any)
  If ActiveWorkbook.XmlMaps.Count > 0 Then
    ActiveWorkbook.XmlMaps.Item(1).Delete
  End If

  ' clear out range to prep for new import
  Range(RangeToImport).CurrentRegion.Cells.Clear

  ' import web XML to destination range
  ActiveWorkbook.XmlImport URL:= _
        "http://www.recipepuppy.com/api/?q=" _
        & URLEncode(Range("SelItem").Value) _
        & "&format=xml", ImportMap:=Nothing, _
        overwrite:=True, Destination:=Range(RangeToImport)

ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

'------------------------------

Public Function URLEncode(EncodeStr As String) As String
' http://www.freevbcode.com/ShowCode.Asp?ID=5137
Dim i As Integer
Dim erg As String

  erg = EncodeStr

  ' *** First replace '%' chr
  erg = Replace(erg, "%", Chr(1))

  ' *** then '+' chr
  erg = Replace(erg, "+", Chr(2))

  For i = 0 To 255
    Select Case i
        ' *** Allowed 'regular' characters
      Case 37, 43, 48 To 57, 65 To 90, 97 To 122
      Case 1        ' *** Replace original %
        erg = Replace(erg, Chr(i), "%25")
      Case 2        ' *** Replace original +
        erg = Replace(erg, Chr(i), "%2B")
      Case 32
        erg = Replace(erg, Chr(i), "%20")
      Case 3 To 15
        erg = Replace(erg, Chr(i), "%0" & Hex(i))
      Case Else
        erg = Replace(erg, Chr(i), "%" & Hex(i))
    End Select
  Next

  URLEncode = erg

End Function
Download the Excel Weekly Meal Planner

You can download a copy of the Excel Weekly Meal Planner and add your own meals and ingredients. The file is in Excel 2007 format, and is zipped. After you unzip the file and open it, enable macros, so you can create the shopping list.
Home

Excel Tips

Excel Files

Products

Blog

Contact

Privacy Policy

Contextures Inc., Copyright ©2013
All rights reserved.

Debra Dalgleish

Last updated: May 22, 2013 7:42 PM

Contextures Excel news by email Email: Name (optional): We respect your email privacy	Excel Weekly Meal Planner *Thanks to Jimmy Peña, who created the Recipe Selector tool in the meal planner.* Select Recipes Enter the List of Meal Items List the Meal Ingredients Select the Weekly Meals Create the Weekly Shopping List Print the Shopping List Watch the Excel Weekly Meal Planner Video Weekly Meal Planner VBA Code Download the Excel Weekly Meal Planner When the kids are in school, and everyone is involved in evening activities, it can be tough to get your family meals organized. To help you make the job easier, you can use this Excel weekly meal planner. Enter your favourite meals, list their basic ingredients, and click a button to create a shopping list for a week's meals. Note: For special occasions, there's an Excel holiday dinner planner that you can use to organize a meal for a large group, or a fancy dinner for friends.. Select Recipes The first step is to select recipes for your weekly meals, either from your own collection, or on the Recipes sheet in the Excel Weekly Meal Planner. Enter an ingredient name in cell A2, then click the Get Recipes button. A macro runs, and pulls a list of recipes for the selected ingredient. To see one of the recipes, click the link. Enter the List of Meal Items The next step is to list your family's favourite dishes on the Meal_Items worksheet. There are a few sample items in the Excel weekly meal planner. You can add new items to the list, or replace the existing meals with your family favourites. In column B, enter the name of the dish, and in column C you can enter a link to a recipe online, or type a cookbook name and page number. List the Meal Ingredients After you have entered a list of meal items, enter the main ingredients for each item on the Meal_Ingredients worksheet. There is a data validation drop down in column B, where you can select the name of one of your meal items. In column C, enter the quantity required, and in column D, enter the unit, such a can, cup or lb. In column E, enter the name of the ingredient. Finally, in column F, enter the category for the ingredient, to help you make a shopping list. Note: There are formulas in columns G and H, which are used to create the shopping list, so don't type in those columns. Select the Weekly Meals After you have entered all the ingredients, you're ready to start the weekly meal planning. On the WeekdayMeals sheet, select up to 3 items for each weekday meal, by using the data validation drop down lists in columns C:E. Create the Weekly Shopping List After you select each day's meals, click the Shopping List button at the top left of the WeekdayMeals sheet. This runs a macro, which uses an advanced filter to copy all the ingredients for the selected meals to the ShoppingList sheet, and sorts the shopping list by ingredient name. As you select each day's meal, that meal's ingredients are marked with a 1 in column G, on the Meal_Ingredients worksheet. On the Meal_Items sheet, in cells G1:G2, there is a criteria range that's used in the advanced filter. It checks for ingredients that are marked with a 1 (or higher) in column G. Print the Shopping List After the shopping list has been created, the items are summarized in an Excel Pivot Table. You can print the list, add any other grocery items that you need, and then head to the grocery store. Watch the Excel Weekly Meal Planner Video To see a quick overview of how the Excel Weekly Meal Planner works, you can watch this short video. Weekly Meal Planner VBA Code Shown below is the code for the weekly meal planner sample file. The first macro -- CreateShoppingList -- goes on a regular code module. The remaining code goes on the sheet module for the Recipes worksheet Put this code on a regular code module. It creates the shopping list, based on the selected meals. Sub CreateShoppingList() Dim wsShop As Worksheet Dim wsIngred As Worksheet Dim wsList As Worksheet Dim wsItems As Worksheet Dim wsPrint As Worksheet Set wsShop = Sheets("ShoppingList") Set wsIngred = Sheets("Meal_Ingredients") Set wsItems = Sheets("Meal_Items") Set wsPrint = Sheets("ShopListPrint") wsIngred.Columns("B:J").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=wsItems.Range("CritShop"), _ CopyToRange:=wsShop.Range("ExtShop"), _ Unique:=False With wsShop .Cells(1, 2).CurrentRegion.Sort _ Key1:=.Range("C1"), _ Order1:=xlAscending, _ Header:=xlYes End With With wsPrint .PivotTables(1).PivotCache.Refresh .Activate End With End Sub Put this code on the Recipes sheet code module (right-click the Recipes sheet tab, click View Code, and paste the code there) Option Explicit Private Sub CommandButton1_Click() Call GetRecipes End Sub '------------------------------ Sub GetRecipes() On Error GoTo ErrorHandler Const RangeToImport As String = "$A$10" ' delete existing xml map (if any) If ActiveWorkbook.XmlMaps.Count > 0 Then ActiveWorkbook.XmlMaps.Item(1).Delete End If ' clear out range to prep for new import Range(RangeToImport).CurrentRegion.Cells.Clear ' import web XML to destination range ActiveWorkbook.XmlImport URL:= _ "http://www.recipepuppy.com/api/?q=" _ & URLEncode(Range("SelItem").Value) _ & "&format=xml", ImportMap:=Nothing, _ overwrite:=True, Destination:=Range(RangeToImport) ProgramExit: Exit Sub ErrorHandler: MsgBox Err.Number & " - " & Err.Description Resume ProgramExit End Sub '------------------------------ Public Function URLEncode(EncodeStr As String) As String ' http://www.freevbcode.com/ShowCode.Asp?ID=5137 Dim i As Integer Dim erg As String erg = EncodeStr ' * First replace '%' chr erg = Replace(erg, "%", Chr(1)) ' * then '+' chr erg = Replace(erg, "+", Chr(2)) For i = 0 To 255 Select Case i ' * Allowed 'regular' characters Case 37, 43, 48 To 57, 65 To 90, 97 To 122 Case 1 ' * Replace original % erg = Replace(erg, Chr(i), "%25") Case 2 ' *** Replace original + erg = Replace(erg, Chr(i), "%2B") Case 32 erg = Replace(erg, Chr(i), "%20") Case 3 To 15 erg = Replace(erg, Chr(i), "%0" & Hex(i)) Case Else erg = Replace(erg, Chr(i), "%" & Hex(i)) End Select Next URLEncode = erg End Function Download the Excel Weekly Meal Planner You can download a copy of the Excel Weekly Meal Planner and add your own meals and ingredients. The file is in Excel 2007 format, and is zipped. After you unzip the file and open it, enable macros, so you can create the shopping list.