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

Darlie247

New Member
Joined
Apr 2, 2022
Messages
13
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1ORDERING: 2022H:\Projects\ITEM ORDERING\[Ordering - Copy.xlsx]2022
2
3
4ITEM NAMESIZEUNITUNIT AMOUNTSUPPLIERSUPPLIER IDLOCAL 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 REMAININGLOT #EXPIRY DATE(S)VALIDATION NEEDED?COMMENTS
5dfsdfs
6fgdfgdfgdf
7Biotix™ Disposable 100mL Reagent Reservoirs100mLCase of80Fisher Scientific12111083491574Yes
8Flasks - Corning, Canted neck/Vented Cap (Orange)70mLCase ofFisher Scientific25103-2520419
9Flasks - Falcon, Canted Neck/Vented cap (Blue)70mLCase of100Fisher Scientific1012610489029
10Kimwipes, Large119wipesEach-Fisher Scientific06-666-11C
11Pipette, Serological 1mL Sterile, Basix1mLCase of1000Fisher Scientific14955231488303
12Pipette, Serological 25mL Sterile, Basix25mLCase of200Fisher Scientific14955235
13Pipette, Serological 5mL Sterile, Basix5mLCase of200Fisher Scientific14955233488304Yes
14Polypropylene Concial Tubes, Falcon15mLCase of500Stores355030
2022
Cell Formulas
RangeFormula
I1I1=CELL("filename")
Cells with Data Validation
CellAllowCriteria
T5ListEach, Bag, Bottle, Box, Case, Mililiter, Pad, Package, Roll, Sheet, Vial
R5:R14ListEach, Bag of, Bottle, Box, Box of, Case of, Kit, Milliliter, Ounce, Pad of, Package of, Roll, Roll of, Sheet, Vial
I5:I14ListEach, Bag of, Bottle, Box, Box of, Case of, Kit, Milliliter, Ounce, Pad of, Package of, Roll, Roll of, Sheet, Vial
C5:C14ListEach, Bag of, Bottle, Box, Box of, Case of, Kit, Milliliter, Ounce, Pad of, Package of, Roll, Roll of, Sheet, Vial
Y5:Y14ListYes
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
VBA Code:
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
 
Upvote 0
Solution
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.
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top