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
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 |