Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Somewhat complex code to copy data from one sheet to another
The code is in the "Groceries Needed" sheet module.
First InputBox... myMeal = a header in sheet named "Groceries Needed". Second InputBox... ColmyMeal = the column of myMeal in sheet "Groceries Needed". Case "Chili" = Chili is the header of column D in sheet named "Recipes". User enters Chili in first InputBox. User enters B in second InputBox. Want to copy the Chili ingredients listed in column D of sheet "Recipes" to column B of sheet "Groceries Needed". However, next week Chili may be in column F of sheet "Groceries Needed" but the ingredients for Chili will always be in column D sheet "Recipes". Code as is returns Case Else. Thanks. Howard Option Explicit Option Compare Text Sub Graba_Grub_Dub() Dim myMeal As String Dim ColmyMeal As String myMeal = InputBox(Prompt:="What Meal...?", _ Title:="DINE TIME", Default:="Your Meal here") 'MsgBox myMeal ColmyMeal = InputBox(Prompt:="What Column...?", _ Title:="The Column", Default:="The Meal Column Here") 'MsgBox ColmyMeal If myMeal = "Your Name here" Or myMeal = vbNullString Then Exit Sub If ColmyMeal = "The Meal Column Here" Or ColmyMeal = vbNullString Then Exit Sub Select Case "myMeal" Case "Chili" Sheets("Recipe").Range("D3:D" & Range("D" & Rows.Count) _ .End(xlUp).Row).Copy Sheets("Groceries Needed") _ .Cells(Sheets("Groceries Needed") _ .Rows.Count, "ColmyMeal").End(xlUp).Offset(1, 0) Case Else MsgBox "NaDa Good myMeal" End Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Somewhat complex code to copy data from one sheet to another
Suggestion...
Store your ingredients lists on a separate sheet from your meals schedule, and name each list same as myMeal (without spaces)... Example: myMeal:=Beef Lasagna IngredientsList:=BeefLasagna OR Beef_Lasagna ...so all you need to do is grab the list into an array and dump the contents into your "Groceries Needed"... Dim vIngrList, rngTarget As Range Const sPrompt$ = "Select the cell where the ingredients list starts" Const sTitle$ = "Insert Ingredients" 'Grab the ingredients list for myMeal vIngrList = Sheets("Lists").Range(Replace(myMeal, " ", "")) 'OR ' vIngrList = Sheets("Lists").Range(Replace(myMeal, " ", "_")) 'Prompt for the location to insert the list Set rngTarget = _ Application.InputBox(Prompt:=sPrompt, Title:=sTitle, _ Default:=ActiveCell.Address, Type:=8) 'Dump the list into the worksheet If Not rngTarget Is Nothing Then _ rngTarget.Resize(UBound(vIngrList), 1)) = vIngrList ...where (as per your sample) myMeal is "Chili" and the ingredients are located on "Lists" in the range named "Chili". As per my sample, myMeal would be "Beef Lasagna" and its ingredients would be located in the range named "BeefLasagna" (or "Beef_Lasagna") on "Lists". HTH -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Somewhat complex code to copy data from one sheet to another
On Thursday, March 14, 2013 6:54:41 AM UTC-7, GS wrote:
Suggestion... Store your ingredients lists on a separate sheet from your meals schedule, and name each list same as myMeal (without spaces)... Example: myMeal:=Beef Lasagna IngredientsList:=BeefLasagna OR Beef_Lasagna ..so all you need to do is grab the list into an array and dump the contents into your "Groceries Needed"... Dim vIngrList, rngTarget As Range Const sPrompt$ = "Select the cell where the ingredients list starts" Const sTitle$ = "Insert Ingredients" 'Grab the ingredients list for myMeal vIngrList = Sheets("Lists").Range(Replace(myMeal, " ", "")) 'OR ' vIngrList = Sheets("Lists").Range(Replace(myMeal, " ", "_")) 'Prompt for the location to insert the list Set rngTarget = _ Application.InputBox(Prompt:=sPrompt, Title:=sTitle, _ Default:=ActiveCell.Address, Type:=8) 'Dump the list into the worksheet If Not rngTarget Is Nothing Then _ rngTarget.Resize(UBound(vIngrList), 1)) = vIngrList ..where (as per your sample) myMeal is "Chili" and the ingredients are located on "Lists" in the range named "Chili". As per my sample, myMeal would be "Beef Lasagna" and its ingredients would be located in the range named "BeefLasagna" (or "Beef_Lasagna") on "Lists". HTH -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion Thanks, Garry. I'll work with the suggestions you have offered and see if can bring it together. I see these things often in code you have written for me and others and I get frustrated because I am often unable assemble them as needed. A lack of understand of some basic fundementals on my part. My problem is I am way smart enough to ask the right questions and way dumb enough to not understand the answer...<G Thanks again, I'm on it. Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Somewhat complex code to copy data from one sheet to another
Thanks, Garry.
I'll work with the suggestions you have offered and see if can bring it together. I see these things often in code you have written for me and others and I get frustrated because I am often unable assemble them as needed. A lack of understand of some basic fundementals on my part. My problem is I am way smart enough to ask the right questions and way dumb enough to not understand the answer...<G Thanks again, I'm on it. Howard I'm here if you need further assistance. <FWIW I've looked at a few 'meal schedule' templates in the past that were fairly well done. I don't know if you're using one of those but it would be helpful knowing more about your specific project. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Somewhat complex code to copy data from one sheet to another
On Thursday, March 14, 2013 10:50:44 AM UTC-7, GS wrote:
Thanks, Garry. I'll work with the suggestions you have offered and see if can bring it together. I see these things often in code you have written for me and others and I get frustrated because I am often unable assemble them as needed. A lack of understand of some basic fundementals on my part. My problem is I am way smart enough to ask the right questions and way dumb enough to not understand the answer...<G Thanks again, I'm on it. Howard I'm here if you need further assistance. <FWIW I've looked at a few 'meal schedule' templates in the past that were fairly well done. I don't know if you're using one of those but it would be helpful knowing more about your specific project. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion Hi Garry, It's a home grown version that someone sent me off forum via personal email.. Since my earlier query was based on some assumptions, which seemed to have changed, after further information was recieved by me. I'll post a link and you can look at it if you care to. I'm still chasing the select case with slow going. In a nutshell, user wants to select a meal from sheet "Meal Options" and have the Meal name and necessary ingredients posted in sheet four, "Groceries Needed" https://www.dropbox.com/s/u4fkxfzz0l...rsion%201.xlsm Howard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Somewhat complex code to copy data from one sheet to another
Hi Howard,
I downloaded the file and I'll have a look at it later this morning... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Somewhat complex code to copy data from one sheet to another
Howard,
Check this site for the various templates I've looked at... http://www.vertex42.com/ExcelTemplat...l-planner.html ...and scroll down to the 'Printable Meal and Menu Planners' section. Personally, I like the 'Monthly Menu Planner with Grocery List' version. I modified it some with Group/Outline to make it more user friendly, but I say it's one of the best (free ones) I've seen! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Somewhat complex code to copy data from one sheet to another
On Friday, March 15, 2013 10:20:18 AM UTC-7, GS wrote:
Howard, Check this site for the various templates I've looked at... http://www.vertex42.com/ExcelTemplat...l-planner.html ..and scroll down to the 'Printable Meal and Menu Planners' section. Personally, I like the 'Monthly Menu Planner with Grocery List' version. I modified it some with Group/Outline to make it more user friendly, but I say it's one of the best (free ones) I've seen! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion Thanks, Garry. No sense re-inventing the wheel. Howard |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Somewhat complex code to copy data from one sheet to another
No sense re-inventing the wheel
I just thought some of these templates might give you some redirection in terms of structure/layout of the overall project. (Not meaning to criticize) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Somewhat complex code to copy data from one sheet to another
On Friday, March 15, 2013 5:03:48 PM UTC-7, GS wrote:
No sense re-inventing the wheel I just thought some of these templates might give you some redirection in terms of structure/layout of the overall project. (Not meaning to criticize) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion I took it as good advice from a knowledgeable person. Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex Compare Values, and Copy/Paste if in One Sheet but Not Oth | Excel Programming | |||
Copy and paste subtotals-complex data range error | Excel Discussion (Misc queries) | |||
Complex Question. Index/Match then Copy/Paste to Summary Sheet | Excel Programming | |||
Copy all data into one sheet and insert origin data... code amend... | Excel Programming | |||
How to Copy Data from on field to another? Complex? | Excel Discussion (Misc queries) |