VBA copying based on common cell value

[kornstalker]

I am trying to make some improvements to a purchase order tracking workbook. Every purchase order is entered in the Open spreadsheet with certain information, PO#, Quoted Delivery, Order Quantity, etc. If a given PO# has multiple line items, each line item is entered in separate rows, but share info such as PO#, Date, Quoted Delivery, etc. More information is entered as we receive the items. The Date Received is entered as each line item is received. The PO Completed Date is entered when all line items have been received for a given PO#.
I would like to write VBA that will make initial entry, receiving updates, and data sorting, easier/faster and more automated to eliminate errors.

Needing to do something to the effect of this:
If I enter the same value in Column A of Row 3 as was in Column A of Row 2, I want the values of Column B, C, & D of Row 3 to auto-populate with the same values from those columns in Row 2. This is to make initial entry easier/faster/more accurate.

If I enter a date in Column O, PO Completed Date, I want it to copy that date to all other rows sharing the same PO# in Column A. This is to make entries when receiving the order easier/faster/more accurate.

If there is a date in Column O, I want those rows copied to the Closed worksheet, and deleted from the Open worksheet. This is to move the data from Open to Closed automatically once a PO Completion Date is entered.

Also......Can I also write code that will merge the cells of Column O on the 'Closed' worksheet if they share a value in Column A? I want to do this to make it easier to sort through old PO#s to see which ones were on time and which ones were late without the clutter. I know merging cells can mess with the sorting and other things later so I don't want to do it unless I'm sure nothing will slip through the cracks in a sorting operation.

I know it was hard enough just figuring out and explaining what exactly I want it to do, so hopefully this makes sense. I've received great help here in the past and I'm sure this is doable as long as I explained it correctly. Thanks in advance

[davesexcel]

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[kornstalker]

Here is a sample copy. In regards to the last part about merging cells on the Closed worksheet....there may be another excel function other than merging that I don't know about that will do what I am asking. I just want to see the PO# represented by a single PO Completion Date on that page. According to how I have described how I want everything else to work, it would show up as multiple lines with the same date. If I tried to see how many PO#s were received late, I would have multiple entries for PO#s with multiple line items and I would have to tally them some other way. Maybe instead of the part about copying PO Completion Date as soon as a date is entered in one of the cells with the same PO#, that it could just leave it blank, but still move it to the Closed sheet. Then only one of each PO# on the Closed sheet would have data there, making it easier to tally.

Hopefully my post makes sense....having a hard time keeping it all straight in my head ha

[davesexcel]

....
Hopefully my post makes sense....having a hard time keeping it all straight in my head ha

Well you probably won't get all the answers at once.

[davesexcel]

This is the code for your 1st step, it belongs in the worksheet module.

Please Login or Register  to view this content.

RightClickStTab.jpg
Workshtchange.jpg
enter158.jpg

[kornstalker]

Yep that does it for the first part!

[davesexcel]

Can you show anything of your attempts on the other steps?

Have you tried the Macro Recorder?

If you don't know how to use your macro recorder, check this out.

http://www.xlorate.com/excel-questio...cro%20Recorder

You are asking a lot of work from others, but you haven't shown anything that shows you put work into this.

[kornstalker]

Yeah I guess maybe I should spend some more time learning about the code and commands and how that relates to the action I want taken. I don't really know the basics so other than writing a few basic formulas for cells or for formatting, I don't mess with much else. I've got some of that stuff going on with the book and more that I will be adding later, but posted on here in the area I don't know much about working with. I have written and used some macros, but again, I have never really known the basic language so I have pieced them together through this forum or other sources. I had a code to execute part of step one, but only enough to autofill one column, not the other two. The one you submitted covers all of them. I have a macro in another book and a version of its code may do what I'm wanting in the last step, moving the rows automatically to Closed, but I haven't tried to integrate that into this yet. I may be able to understand it enough to copy it from another book's macro, make changes to match it up with this one, and get it to work. I also know that depending on how the stuff is written, it may work but not be the most efficient or may have holes. The few times I've been on here and asked questions, the answers came quickly and worked well. I thought it was easy stuff for people familiar with writing it once I described what I was looking for it to do. Maybe it's not as easy for others as I thought. I'm still having a hard time visualizing what I need it to do, b/c it has to be functional for a number of people who will use the spreadsheet for different purposes. I wanted to get my whole plan out there as best I could when I asked for help b/c I didn't want a second part of my plan to undue or change how the first step was executed. I also just realized that an entry in Column H, SO#, is another column that when entered again in the spreadsheet, should autofill Column J from the original row. I'll read some more I guess and get some basics, then I can probably figure out how to piece some more of these things in there. Thanks