Hello all. Always a pleasure. Every month we reconcile our inventory, but it's a tedious process. I am hoping someone can help me with some code that will automate this process.
I have one summary sheet (CIT Summ) where I list all of our materials, the beginning balance for the month (Pre-freeze - listed on a separate sheet in the same workbook), and the ending balance for the month (Updated Inventory - also listed on a separate sheet in the same workbook). The CIT Summ tab looks something like this.....
________________beg qty____beg $____end qty____end $
resin.......................100...........200..........110............220
regrind....................50.............100..........40..............80
boxes.....................100............100..........100............100
pallets....................100............200...........110...........220
etc.
Total whse #1
________________beg qty____beg $____end qty____end $
resin
regrind
boxes
pallets
etc.
Total whse #2
We have multiple raw materials (line items in CIT Summ) and multiple warehouses. In the Pre-freeze tab we have ALL of the beginning balances, qty & $, for all the materials for all the warehouses. Same thing for the Updated Inventory tab. There we show ALL of the ending balances by quantities and $ for all warehouses.
We have multiple occurences of each raw mat'l. Meaning there might be 50 lines of resin for warehouse #1, and 75 lines of regrind for warehouse #1, so I go to those tabs and do an Auto Filter and Subtotal so that I can get the subtotal by type of raw mat'l and warehouse. I then copy and paste those subtotals to the CIT Summ tab.
For example, to fill in the figures for resin in CIT Summ, I go first to the Pre-freeze tab, auto filter by resin in warehouse #1, copy and paste those figures back into CIT Summ, and proceed with the second line item, i.e. regrind. I do this for both the beginning balances and ending balances. Did I mentioned that we have multiple (a lot) of line items and multiple (a lot) of warehouses?
It would be nice if I could press a button and the macro populates all of the fields by line item, by warehouse, and by beginning and ending quantities and $. The order of the line item in CIT Summ always remains the same. Meaning that it'll always be resin, regrind, boxex, etc. in that order. I don't know if this makes a difference or not. Also, in the CIT Summ tab the warehouse # is only listed at the bottom for the total, but in the Pre-freeze and Updated Inventory tabs each line includes the warehouse #. Is this something that can be easily accomplished? Thank you all for your help. Have a great day.
[/img]
I have one summary sheet (CIT Summ) where I list all of our materials, the beginning balance for the month (Pre-freeze - listed on a separate sheet in the same workbook), and the ending balance for the month (Updated Inventory - also listed on a separate sheet in the same workbook). The CIT Summ tab looks something like this.....
________________beg qty____beg $____end qty____end $
resin.......................100...........200..........110............220
regrind....................50.............100..........40..............80
boxes.....................100............100..........100............100
pallets....................100............200...........110...........220
etc.
Total whse #1
________________beg qty____beg $____end qty____end $
resin
regrind
boxes
pallets
etc.
Total whse #2
We have multiple raw materials (line items in CIT Summ) and multiple warehouses. In the Pre-freeze tab we have ALL of the beginning balances, qty & $, for all the materials for all the warehouses. Same thing for the Updated Inventory tab. There we show ALL of the ending balances by quantities and $ for all warehouses.
We have multiple occurences of each raw mat'l. Meaning there might be 50 lines of resin for warehouse #1, and 75 lines of regrind for warehouse #1, so I go to those tabs and do an Auto Filter and Subtotal so that I can get the subtotal by type of raw mat'l and warehouse. I then copy and paste those subtotals to the CIT Summ tab.
For example, to fill in the figures for resin in CIT Summ, I go first to the Pre-freeze tab, auto filter by resin in warehouse #1, copy and paste those figures back into CIT Summ, and proceed with the second line item, i.e. regrind. I do this for both the beginning balances and ending balances. Did I mentioned that we have multiple (a lot) of line items and multiple (a lot) of warehouses?
It would be nice if I could press a button and the macro populates all of the fields by line item, by warehouse, and by beginning and ending quantities and $. The order of the line item in CIT Summ always remains the same. Meaning that it'll always be resin, regrind, boxex, etc. in that order. I don't know if this makes a difference or not. Also, in the CIT Summ tab the warehouse # is only listed at the bottom for the total, but in the Pre-freeze and Updated Inventory tabs each line includes the warehouse #. Is this something that can be easily accomplished? Thank you all for your help. Have a great day.
[/img]
