PDA

View Full Version : Adding functionality to an existing Excel Workbook (example pictures included)



navybrother2
08-03-2017, 06:39 AM
Apparently I can't link pictures :(

I am trying to add functionality to current macros that I have working to be more robust and handle more unique situations.

Basically, I've written a macro that takes all the sheets from "Main Frames" up until "cables and rods" and throws them all on a master sheet. I am now trying to add functionality to the master sheet that allows for manual entry into the "Load #" and "Quantity" sections, and based upon those entries, creates a load list for each unique tag (just numbers in this case), keeps a running tally of each loads' weight, and lets me know when "QTY" == "QTY SHIP".

There are some tricky parts to this. Both "Load #" and "Qty Ship" columns are in varying column locations on the several lists that make up the Master Sheet.

Also, whenever there are multiple "LD #" tags in a certain cell, there will also be multiple entries into the "QTY ship" column... eg. if "1 2" is entered into "LD #" cell, then the "Qty ship" cell could look like "10 2"

Also, I'd like a running total of any Load #'s weight.. meaning that as the "LD #" tags are being added and quantities filled in, I'd like to have a place where a person could look and see how much weight that load currently has on it. Also this functionality would have to work with the cells that have multiple entries as well

As far as the quantity comparison (comparing quantity to quantity shipped), I was thinking a conditional formatting would work. something like if QTY Ship > Qty make it red, if QTY Ship = QTY make it green, and no color until then.

Really at the end of my capability in Excel and am looking to enlist a Master Guru to show me the light!
(google drive copy of example file can't be attached because apparently I can not post links?)

SamT
08-03-2017, 10:37 AM
25 sheets, and only 3 are named? I ain't gonna bother until you give each sheet a meaningful name. :banghead:

I suggest that you create a comprehensive Template (*.xltx file type) and place all Macro Procedures in it, then when you have a new Order to process, open the Template and save it named as you would any new order.

After the Template is perfected and has been in production long enough to find all bugs, you can think about extending the project with Custom Data Entry UserForms in a Master Workbook that uses the Template. This would go a long way in speeding up the entire process and preventing any User errors and omissions... But that is for later.

Since I suspect that some of this is not for public view, you may contact me via PM. Please use the public forum for all non confidential questions and information. Note to all others, this is NOT a blanket invitation to PM me.

SamT
ETN2, '69 to '78