Microsoft Excel - Update stock with macro
Asked By John on 28-Nov-12 09:46 PM
Harry Boughen replied to John on 29-Nov-12 12:49 AM
You possibly don't even need a macro. If you select a cell in your stock sheet and call up the form on the Data tab/menu, excel will generate a form for you to scroll through your list and delete and make new entries.
Other than that we would need a lot more detail to be able to help.
John replied to Harry Boughen on 29-Nov-12 10:19 AM
Sir, I added my file here.
I have a workbook contains 3 sheets. Sheet 1 is daily sale. Sheet2 is purchase and sheet 3 for updating the stock from sheet 1 and sheet2 as per the date of purchase and sale. Once a stock updated to sheet 3 it will not update again. Colour should change with white, background 1, darker 5% so it should not update again. Purchase will be happened only some times, but sales in regular basis. If there is opening in the stock time it should add to the stock in hand then if there is purchase, it should add to stock in hand then minus sales for the day from stock in hand. Each day’s entry should add to stock in hand. How can possible with macro.
John replied to Harry Boughen on 29-Nov-12 11:14 AM
Sir I don't understand that, how is it? Can you describe it for me.
Thanks and Regards
Harry Boughen replied to John on 29-Nov-12 04:31 PM
You appear to be using either Excel 2007 or 2010. You have to get into Customise Ribbon and add the Form... option to your ribbon and then it will be accessible.
For Excel 2010:
Select the File, Tab, Options, Customise Ribbon, Choose Commands From, Commands Not on Ribbon, Form.... (This will be highlighted)
Then in Customise the Ribbon select Data, Click New Group, Select NewGroup, Click Rename (Type DataEntry), Select DataEntry (This will be highlighted) Click the Add>> button between the sections. The Form... will now be available on your ribbon. Select a cell in your catalog, click Form... and a form will open which will allow you to step through your data, edit, delete, add search etc.
On your spreadsheet, the simplest way to do it, I think is to have a formula in your Current stock column (Opening Stock +Purchases -Sales) and then a simple macro that you run daily to copy the Current Stock values to the Opening Stock column. Is this the sort of thing you had in mind? If so, you could easily record a macro to do it yourself.
Harry Boughen replied to John on 29-Nov-12 06:13 PM
Maybe this is something like what you want.
The macro updates the current stock holding column but doesn't affect the opening stock. When the macro runs, it places the current date on the sheet and it will not run again until the cell is cleared or the date on the computer changes. Conditional formatting shades the column to indicate that the stock update has been done.
John replied to Harry Boughen on 29-Nov-12 10:25 PM
Dear Sir, there is a doubt. Example CodeA10001 Opening Stock = 151, Purchase = 10, Sale =06 then how Stock in Hand will come 145. How is the calculation you made for.
Thanks and regards
Harry Boughen replied to John on 29-Nov-12 10:38 PM
I had made several runs through the function as I tested it and so there were several sets of addition and subtraction from the original opening stock. I did not think to clear the data or that you would be checking the calculations with my faked in data.
I have assumed that you wanted your (say) start of month stock holding to be kept (perhaps until you did a stocktake) and that you wanted the current stock to be updated on a daily basis.
John replied to Harry Boughen on 30-Nov-12 12:30 PM
Harry Boughen replied to John on 01-Dec-12 01:40 AM
I am afraid that I do not quite understand what it is that you want to do. Do you just want to copy your whole stocklist with the opening stock?, Current stock?, sales and purchases or just those that have changed?
The other thing is, I do think you should at least have a go at learning to do some of this yourself and then ask for help when you get stuck rather than just asking for it to be done for you. Not that I mind helping you out, but it might make it a bit easier to understand what you want.
John replied to Harry Boughen on 02-Dec-12 01:05 AM
Dear Sir, we got opening stock when we make Inventory (Manually count all items in a shop). Then we got a actual stock of an item. Then we add to that all value to computer as opening stock. When the purchase and sale happened that should add and subtract from opening stock then we get stock in hand and present stock. Sir I will explain you what I meant, we daily add sale and purchase the workbook and take the stock in hand. I want to keep in record of daily sale, purchase and stock to new sheet. I have a workbook name sales register, purchase register, and stock register with all code and all item name contains there. From the present workbook I want to insert every particular day's sale, purchase and stock to new sheet. I want to insert to column to new workbook becuse there is value of each items. Sir now I am just learning about macro. I don't have more knowledge.
Harry Boughen replied to John on 02-Dec-12 02:13 AM
Is there one workbook with sheets named SalesRegister, PurchaseRegister and StockRegister or are there three separate workbooks with just a page in each?
John replied to Harry Boughen on 02-Dec-12 03:14 AM
There only one workbook with sheets named SalesRegister, PurchaseRegister and StockRegister sir.
And sheet is like below
Code, Name of Items, MRP, Total Price
Sir, I would like to insert every day's sale, purchase, Stock after the column of Name of Items to the depending sheet. Not overwirte the columns just add column in each sheet after Name of Items. Is it possible sir?
Harry Boughen replied to John on 02-Dec-12 03:31 AM
Anything is possible. You say that you want the data to go after the name of item column. What is to happen to MRP and Total Price columns?
John replied to Harry Boughen on 02-Dec-12 04:36 AM
MRP and Total Price should be there. Then I can calculte Present Stock * MRP = Total Price so I can assume Total coast of the stock.
Thank you sir.
Harry Boughen replied to John on 02-Dec-12 06:05 AM
Will each sheet look like
SN, ItemList, MRP, TotalPrice, Day1, Day2, Day3.......
or will it be
SN, ItemList, MRP, TotalPrice, Dayx?
John replied to Harry Boughen on 02-Dec-12 06:19 AM
Sir MRP is constant Total price is last appended dated column (vlue) * MRP
SN, ItemList, MRP, Day1, Day2, Day3....... * MRP = TotalPrice (Total coast of item)
Latest Day * MRP
Thanks & Regards
Harry Boughen replied to John on 02-Dec-12 05:12 PM
Would it be better to have:
SN, ItemList, MRP, CurrentValue = Current Day*MRP, CurrentDay, PreviousDay, etc
Just a thought.
John replied to Harry Boughen on 02-Dec-12 09:37 PM
Ok Sir, If it will be better then Sir you do like that.
Harry Boughen replied to John on 03-Dec-12 07:44 PM
The attached files will do what I think you want.
I have modified your file and put in a number of checks to make the operation more robust. Both files have to be in the same directory and if you change the file names you must change them to match in the macro as well.