Copy & Paste Specific cells from a Row based on Drop-Down List

[Darlie247]

I'm VERY new to VBA and have exhausted much searching online to find VBA that matches exacly what I am trying to achieve.
I have an "Ordering" sheet and a "Validation" sheet. I want to be able to copy and paste data from Ordering to Validation (next available/empty row) only if the dropdown list in Y column is selected as Yes. However, I do not need the whole row of data to be copied over. I need only the data in columns in A, E, F, P, W, and X copied. The Ordering sheet is also constantly being added to. I'm very confused on how to reference Ranges that are dynamic.

I don't even know how to start going about this? Any help would be greatly appreciated

Ordering - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	ORDERING:	2022							H:\Projects\ITEM ORDERING\[Ordering - Copy.xlsx]2022
2
3
4	ITEM NAME	SIZE	UNIT	UNIT AMOUNT	SUPPLIER	SUPPLIER ID	LOCAL ID	ORDER QUANTITY	ORDER UNIT	ORDER UNIT AMOUNT	ORDER TECH	ORDER DATE	APPROVED BY	APPROVAL DATE	CG#/PR#	PO#	RECEIVED QUANTITY	RECEIVED UNIT	RECEIVED UNIT AMOUNT	RECEIVING TECH	RECEIVED DATE	QUANTITY REMAINING	LOT #	EXPIRY DATE(S)	VALIDATION NEEDED?	COMMENTS
5	dfsdfs
6	fgdfgdfgdf
7	Biotix™ Disposable 100mL Reagent Reservoirs	100mL	Case of	80	Fisher Scientific	12111083	491574																		Yes
8	Flasks - Corning, Canted neck/Vented Cap (Orange)	70mL	Case of		Fisher Scientific	25103-25	20419
9	Flasks - Falcon, Canted Neck/Vented cap (Blue)	70mL	Case of	100	Fisher Scientific	1012610	489029
10	Kimwipes, Large	119wipes	Each	-	Fisher Scientific	06-666-11C
11	Pipette, Serological 1mL Sterile, Basix	1mL	Case of	1000	Fisher Scientific	14955231	488303
12	Pipette, Serological 25mL Sterile, Basix	25mL	Case of	200	Fisher Scientific	14955235
13	Pipette, Serological 5mL Sterile, Basix	5mL	Case of	200	Fisher Scientific	14955233	488304																		Yes
14	Polypropylene Concial Tubes, Falcon	15mL	Case of	500	Stores	355030
2022

Cell Formulas
Range	Formula
I1	I1	=CELL("filename")

Cells with Data Validation
Cell	Allow	Criteria
T5	List	Each, Bag, Bottle, Box, Case, Mililiter, Pad, Package, Roll, Sheet, Vial
R5:R14	List	Each, Bag of, Bottle, Box, Box of, Case of, Kit, Milliliter, Ounce, Pad of, Package of, Roll, Roll of, Sheet, Vial
I5:I14	List	Each, Bag of, Bottle, Box, Box of, Case of, Kit, Milliliter, Ounce, Pad of, Package of, Roll, Roll of, Sheet, Vial
C5:C14	List	Each, Bag of, Bottle, Box, Box of, Case of, Kit, Milliliter, Ounce, Pad of, Package of, Roll, Roll of, Sheet, Vial
Y5:Y14	List	Yes

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Ordering" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter "Yes" in column Y and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 25 Then Exit Sub
    If Target = "Yes" Then
        With Sheets("Validation")
            Intersect(Rows(Target.Row), Range(" A:A, E:E, F:F, P:P, W:W, X:X")).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
        End With
    End If
    Application.ScreenUpdating = False
End Sub

 

[Darlie247]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Ordering" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter "Yes" in column Y and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 25 Then Exit Sub
    If Target = "Yes" Then
        With Sheets("Validation")
            Intersect(Rows(Target.Row), Range(" A:A, E:E, F:F, P:P, W:W, X:X")).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
        End With
    End If
    Application.ScreenUpdating = False
End Sub

Thank you so much!! This is exactly what I needed!!

For some reason I thought this would be so much more complicated and longer than this lol

 

[mumps]

You are very welcome.