Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Copy last entry in specific columns on one sheet to specified different sheet when multiple conditions are met

0

I have a workbook that is created of inventory items at work. I have a data sheet where I enter information about items I have to add to the inventory room. On this sheet I have the first column as the customer followed by the part number, revision, quantity, cart number and specific shelf location. Then I have 4 sheets following the data sheet that make up the 4 cart locations. I want to be able to have excel auto place the entries I make on the data sheet to the correct sheet for the corrosponding cart number entered tin the first blank cell at the end of the listed parts under that specific customer. The 4 different sheets  folowing my data sheet are named "Cart 1, Cart 2, Cart 3, and Cart 4. All these sheets have multiple shelf locations and multiple customers with parts listed under them. There are a couple cases where custome parts are located on more then one cart and even different shelf locations so I need to be specific with where I want my data sheet entries to copy to. At some times I think I need to use an if function with multiple conditions in code somehow but I cant seem to figure out just how to code this. I have tried to cut and paste from many other forms the pieces I think will benifit my task however I have not been able to piece together a working VBA code to suit my needs. This sounds like WAY too much information and confusing as ever but I hope someone might be able to help me find a VBA code to help with me. Hope this question isnt too confusing.

Thanks for any help. It will all be much appreciated!

Answer
Discuss

Discussion

Laycielee

This sounds a bit confusing but possible with VBA though we will need to create (not "find") the code or give you clues on it.

Picking the right sheet to copy to seems straightforward but I don't understand how you'd like data on each of the four Cart sheets-- at first it sounded like the first blank row on the correct sheet but then you qualified it.  Please explain by revising your question (and preferably attaching a suitable test file).
John_Ru (rep: 6102) Nov 19, '20 at 5:14 pm
Screenshot 1 is my DATA SHEET. Red cells have pre-existing data. I want the yellow cells of data entered at the bottom to be added to the correct sheet for the cart specified in the green highlighted cell at the first blank line under the customer specified in the blue colored cell. In the second screenshot I show my sheet "Cart 1" and the shelf and customer columns as laid out. Again (red cells are pre-existing data) I want the yellow cells from the data sheet to be copied to the yellow cells on the "CART 1" sheet shown.


laycielee (rep: 2) Nov 19, '20 at 6:37 pm
for some reason I can not post screeshots
laycielee (rep: 2) Nov 19, '20 at 6:39 pm
this is the layout of my CART 1, 2, 3 and 4 sheets:

TOP SHELF:   SHELF: 2         SHELF: 3   CUSTOMER 1   CUSTOMER 2   CUSTOMER 3   CUSTOMER 4   PN REV QTY PN REV QTY PN REV QTY PN REV QTY
laycielee (rep: 2) Nov 19, '20 at 6:45 pm
THAT DIDNT WORK EITHER! HOW ANNOYING
laycielee (rep: 2) Nov 19, '20 at 6:45 pm
For lack of a better description here is the layout for the top 3 rows of my "CART" sheets. The shelves 1-5 take the first row and some hold more then 1 customer so the customers take the second row. The third row is the data entered underneith. I dont know if anyone can help me at all but this is really giving me a headache at this point and not being able to implanment a simple screenshot or 2 might make this question imposible to understand!

:(


CELL A1= “TOP SHELF” – CELL B1=BLANK – CELL C1=BLANK CELL A2=”CUSTOMER1” – CELL B2=BLANK – CELL C2=BLANK CELL A3= “PART NUMBER” – CELL B2=”REVISION” – CELL C3=”QUANTITY"
laycielee (rep: 2) Nov 19, '20 at 6:52 pm
messed up AGAIN CELL B3=REVISION not cell B2
laycielee (rep: 2) Nov 19, '20 at 6:54 pm
Please edit your question. Vital parts of your question have no business in the Discussion section. You can edit your question as often as you may need. Just, don't shift the goal posts.
A complex Excel task becomes simple when you narrow it down to a source and target cell, like, "I want whatever I enter in Master!A:A to be copied to Cartx!C:C where x is in Master!B:B". Ideally you would attach a copy of your workbook for us to see, learn, play and test.
We can't do the latter two with a screen shot. That's why this forum doesn't support picture. We want the real stuff.
Variatus (rep: 4889) Nov 19, '20 at 8:36 pm
Please follow advise from @Variatus above. 

Even if you could provide sceeen shots of complex sheets, that would just mean that helpers would need to recreate your spreadsheet for themselves before attempting a solution (and that would be too time consuming)
John_Ru (rep: 6102) Nov 20, '20 at 2:51 am
how do i upload an example workbook?
laycielee (rep: 2) Nov 20, '20 at 12:47 pm
Edit your Question and (below the text) you will see buttons allowing you to pick files to upload files. Then click Submit.

Kindly be sure that your question reflects the file you're sending (or edit it to suit) so we don't see differences between the two.
John_Ru (rep: 6102) Nov 20, '20 at 12:48 pm
I have uploaded an example workbook. I am looking to get the infromation in the yellow colored cells at the bottom of the "DATA" worksheet to automaticaly copy to the sheet for the cart number specified in the blue colored cell and shelf specified by the green colored cell, at the first empty row for the customer shown in the purple colored cell.
laycielee (rep: 2) Nov 20, '20 at 12:50 pm
Since we both expaned so much time on this, allow me to ask a question. Why would one create a list one doesn't need at a time when it isn't wanted? (Convenience?)
What you need is a cross-referenced list of either one customer, one shelf, or one item. A global list of all shelves with all customers and all items is just a means to that end.
You need it when you want to look at it. That is most definitely not at the time you are busy entering data.
Is there a point in creating this list at the time it's convenient so that it will be ready when needed? The solution I posted proves the answer: No. Such a list can be created in the blink of an eye at the cick of a button. The required technology is already deployed in your workbook. It's just not used to create the report you really need at the time when you really want it.
Variatus (rep: 4889) Nov 21, '20 at 7:23 pm
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login