Somewhat complex code to copy data from one sheet to another

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

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
 
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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

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/u4fkxfzz0lhrio4/Grocery -Recipe List Teat Version 1.xlsm

Howard
 
Howard,
Check this site for the various templates I've looked at...

http://www.vertex42.com/ExcelTemplates/meal-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
 
Howard,

Check this site for the various templates I've looked at...



http://www.vertex42.com/ExcelTemplates/meal-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
Click to expand...

Thanks, Garry.

No sense re-inventing the wheel.

Howard
 
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 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
Click to expand...

I took it as good advice from a knowledgeable person.

Howard
 
Back
Top