How to append range of cells from one sheet to another sheet when an item is selected from drop down list

[andygame]

Hi - I am in the midst of creating a spreadsheet that will effectively create a list of ingredients when a specific menu is selected from a dropdown list. Having stumbled around for hours trying to do this with basic excel commands I have been reading through various vba techniques and feel that this is the way forward. I have found bits of code that allow me to do some of the bits I want to do - but not all. I will explain what I want to achieve and then see if anyone can help . . . .
I'm using Excel 2016 and have a spreadsheet with 3 sheets in it. Input is the first sheet and is where the user will be prompted to select a menu from a dropdown list. Test Area is the second sheet, and this is where I want the data to appear (it is currently blank and unformatted). Menus is the third sheet - this features all the ingredients relating to a particular menu.

Test Menus 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2			Quantities for 50 Servings		Detail		Supplier		Unit Product Code		Unit Cost		Multi Product Code		Multi Pack Cost		No. Units in Multi		Units Req		Multi Req		Total Costs
3	Caesar Salad
4	Garlic Powder		65g		East End Garlic Powder (100g)		Tesco		n/a		£1.15								1				£1.15
5	Mayonnaise		1.44 litres		Tesco Light Mayonnaise (500ml)		Tesco		n/a		£0.65								3				£1.95
6	Anchovy Fillets		160g		Cantobrian Anchovy Fillets (100g)		Tesco		n/a		£1.90								2				£3.80
7	Parmesan Cheese		270g		Grated Parmigiano Reggiano (100g)		Tesco		n/a		£2.20								3				£6.60
8	Worcestershire Sauce		40 ml		Lea & Perrin's Worcestershire Sauce (150ml)		Tesco		n/a		£1.60								1				£1.60
9	Dijon Mustard		40 ml		Grey Poupon Dijon Mustard (215g)		Tesco		n/a		£1.40								1				£1.40
10	Lemon Juice		120 ml		Lemon Juice (500ml)		Tesco		n/a		£1.10								1				£1.10
11	Salt		To taste		Tesco Table Salt		Tesco		n/a		£0.35								1				£0.35
12	Black Pepper		To taste		Schwartz Black Pepper Grinder (35g)		Tesco		n/a		£3.00								1				£3.00
13	Olive Oil		480 ml		Filippo Extra Virgin Olive Oil (500ml)		Tesco		n/a		£3.75								1				£3.75
14	Bread		50 thick slices		Hovis Soft White Thick Slices (16		Tesco		n/a		£1.10								3				£3.30
15	Romaine Lettuce		24		Farm Fresh Romaine Lettuce (2 pack)		Booker		127355		£1.29		127354		£6.45		5		2		2		£15.48
16																			Estimated Total:				£43.48
Side Dishes

Cell Formulas
Range	Formula
W4:W15	W4	=SUM(K4*S4)+SUM(O4*U4)
W16	W16	=SUM(W4:W15)

What I want to do is:
When a user selects a menu from the dropdown list - the code selects a range of cells from the Menus sheet and pastes it into the Test Area sheet.
I then want the user to be able to select from a second dropdown list and repeat this process appending the ingredients into the Test Area sheet.
Ideally, the info in Menus needs to come over to the Test Area sheet EXACTLY as it is in the Menus sheet - ie. column widths, row heights, currency formatting, formulas, font style, size etc.
The desired result would be that the user can see all the ingredients required for a given meal plan.
Any help would be gratefully received.

 

[My Aswer Is This]

This can be done with Vba.
But when using vba we need exact Details

Now you said:
When a user selects a menu from the dropdown list
So where is this dropdown list.
I know you said:
Input is the first sheet and is where the user will be prompted to select a menu from a dropdown list
But where on the Input sheet is this drop down list
Like is it in Range("A1") on sheet named "Input"

So If the user selects "Apple Pie" Where on the "Menus" sheet will it find the ingredients for Apple Pie?

And where on the Test Area sheet do you want this pasted?

I would suggest giving each Menu Item a Named Range so the Range named "Apple_Pie"
would be pasted into sheet Named Test Area if Apple Pie was selected in the dropdown list
When you say Drop down list I assume you mean a DataValidation List

 

[andygame]

This can be done with Vba.
But when using vba we need exact Details

Now you said:
When a user selects a menu from the dropdown list
So where is this dropdown list.
I know you said:
Input is the first sheet and is where the user will be prompted to select a menu from a dropdown list
But where on the Input sheet is this drop down list
Like is it in Range("A1") on sheet named "Input"

So If the user selects "Apple Pie" Where on the "Menus" sheet will it find the ingredients for Apple Pie?

And where on the Test Area sheet do you want this pasted?

I would suggest giving each Menu Item a Named Range so the Range named "Apple_Pie"
would be pasted into sheet Named Test Area if Apple Pie was selected in the dropdown list
When you say Drop down list I assume you mean a DataValidation List

Hi - Thanks for looking at this for me. Apologies if my description is a bit confusing. I've moved on since this post and have managed to create a macro that copies a specified range of cells from one sheet and pastes them into another sheet (in the next available row).

Sub Range_Find_Method()
'Finds the last non-blank row on the Test_Area sheet and sets the variable lRow to that number plus 1.

Set DstSht = Worksheets("Test_Area")
Set sht = Worksheets("Side_Dishes")

Dim lRow As Long

lRow = DstSht.Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row


'Increases value of lRow by one so that `paste' occurs below merged cells

lRow = (lRow + 1)


'Changes the row Height ready for headings

Rows(lRow).RowHeight = 50


'Selects the range of ingredients on the source sheet for this specific macro name.

sht.Range("A56:W61").Copy DstSht.Range("A" & lRow)


End Sub

This seems to work when I trigger the macro manually.

I intend to give this macro a name (example: Caesar_Salad). I would look to trigger this macro when Caesar Salad is selected on the dropdown menu. (I have a bit of code that I think will do this).

I am totally new to VBA and whilst I have some programming experience in that I understand what the code is doing - I'm having a lot of trouble with syntax.
My current issue is that I am trying to select a range of cells - starting at A (lRow) : W (lRow), where lRow is the next available row number.

DstSht.Range("A" & lRow : "W" & lRow).Select (this line of code does not currently work and I'm not sure why)

All I am trying to achieve is to select the above range, merge the cells, and then fill it with a colour to provide a visual break between menus that are pasted into the Test Area sheet.

Thanks for the advice on creating a named Range for each menu - will try that.
Yes - the dropdown lists are data Validation lists - and there are several. The `Input' page basically collects various bits of information - user would select a number of different main courses together with a number of accompanying side dishes. As each dish is selected on the `Input' sheet, the corresponding list of ingredients would be pulled from the Menu sheet and copied into the Test Area sheet.

Hope this is clearer and thanks again for your help.

 

[My Aswer Is This]

Looks like your finding code elsewhere and doing some on your own.
So I will move on and let you work on this yourself. Glad to see your trying to do this on your own.

 

[My Aswer Is This]

If I were to write the script this is what I would do.
On Sheet named "Input"
Range("A1") you put your data validation list with all your Menu Names.
Such as Apple_Pie or Milk or coffee
These Menus as located on a sheet named "Menus"

And the Menus area is given a Named Range like "Milk" Or "Coffee"
etc.
Now when you select Coffee from the data validation list the Range named coffee is copied to the sheet named "Test Area"

Now the script I provide runs automatically when you select a Value in the Data validation

On the sheet named Input put this script
You do not need a new script for every menu

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  5/16/2021  11:56:20 AM  EDT
If Target.Address = Range("A1").Address Then
Dim ans As String
Dim Lastrow As Long
Lastrow = Sheets("Test Area").Cells(Rows.Count, "A").End(xlUp).Row + 2
ans = Target.Value
Sheets("Menus").Range(ans).Copy Sheets("Test Area").Cells(Lastrow, 1)
End If
End Sub

 

[andygame]

If I were to write the script this is what I would do.
On Sheet named "Input"
Range("A1") you put your data validation list with all your Menu Names.
Such as Apple_Pie or Milk or coffee
These Menus as located on a sheet named "Menus"

And the Menus area is given a Named Range like "Milk" Or "Coffee"
etc.
Now when you select Coffee from the data validation list the Range named coffee is copied to the sheet named "Test Area"

Now the script I provide runs automatically when you select a Value in the Data validation

On the sheet named Input put this script
You do not need a new script for every menu

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  5/16/2021  11:56:20 AM  EDT
If Target.Address = Range("A1").Address Then
Dim ans As String
Dim Lastrow As Long
Lastrow = Sheets("Test Area").Cells(Rows.Count, "A").End(xlUp).Row + 2
ans = Target.Value
Sheets("Menus").Range(ans).Copy Sheets("Test Area").Cells(Lastrow, 1)
End If
End Sub

Thanks for this - I think you now understand what I'm trying to do. Thanks also for the code, I currently seem to have lots of small bits of code and I think your code will tidy everything up.
Its a slow process, but I'm starting to get the hang of the code and syntax - used to do a lot of programming in Basic many years ago and although the terminology has changed, the methodology seems to have remained pretty much the same.
Thanks again.

 

[My Aswer Is This]

Now if your Named Ranges may need to expand in height or width.
We could use a Excel Table for the Ranges.
A Excel Table is a Range on a Sheet or Sheets that can automatically expand as you add more Data as time goes on.
It you needed that let me know.

Let me know if you need more help.