07-17-14, 12:56 #1Registered User
- Join Date
- Jul 2014
Unanswered: Creating a catalog purchase order system help
I'm a Graphic Designer by trade and while I know basic Excel this is getting into foreign territory for me.
So I have been tasked with putting together our company's product price list (with +800+ products that are all broken into categories/tabs) into a version that users can select quantities per item and see a running list on the page of their total. No problem, I have this figured out.
Each item has a column for a part number, description, price, and quantity...with the quantity defaulted to zero. I need to get this so that if user wants an item they can change the quantity to whatever they want, and press the add/update purchase order button and have this values of that item's row add to a separate sheet. Additionally, if they were to change value to zero and press the button again, it would remove the item.
So right now each worksheet in the book is like this:
Ideally I guess each worksheet would have a Macro that would copy the row (but as column-order G B C F H) to a sheet in that book named "Purchase Order". If the user changes a quantity of an item to 0, and re-clicks the button, it removes said row from the "Purchase Order" worksheet. This would need to work through the 30+ other tabs, and append the rows below already entered values as the user continues to add products to their purchase order (starting on B22 on the Purchase Order worksheet)
My concerns are adding/deleting products in the future/other staff having to update this and not needing to do crazy changes to the macro if, say I need to add 5 more products to a tab.
I will take ANY help, insight, direction, or guidance anyone can shed on this issue. I greatly appreciate it!
07-21-14, 06:07 #2Grumpy old man (training)
Provided Answers: 8
- Join Date
- Sep 2006
- Surrey, UK
You don't need to duplicate the code for each sheet. Just create an ActiveX button on each sheet, and have it call the same code each time.
With regard to copying the selected values to a PO sheet, I would advise you to investigate named ranges (if you haven't already). You can use a naming convention (e.g. Cat1Item, Cat1Descr, Cat1Qty, Cat2Item, Cat2Descr, Cat2Qty) to name all the columns that you need, and then set up a couple of loops for processing. The outer loop will process the sheets in turn, the inner loop will process the columns in turn. I wouldn't worry about copying the line totals from each sheet to the PO sheet - you can do this either in the code or by generating a formula for each line. The named ranges themselves can either extend to the full height of the columns, or just the populated section. If you pick the former, you will need to code for terminating the inner loops when you encounter empty strings; if you pick the latter, you will need to account for allowing users to add new values to the lists.
Deleting items from a list can be a problem, but I have a workbook that allows users to remove items from it and shuffles up any remaining items that were below it, removing blank lines from the end result. I can sort the code out for you if you like.10% of magic is knowing something that no-one else does. The rest is misdirection.
Beers earned: 1