Stock In/Out Sheet - Help to make more efficient

[JDobbsy1987]

Hi All,

I was hoping someone could help either re-design the way this sheet works or help make it more efficient.
I have attached the spreadsheet in question (this sheet contains dummy info but the sheet itself is in the exact format as the one i use, all i have done is replace the data in it).

Basically the 2 main tabs in question are 'Stock' and 'Change Log'

Stock Tab
This sheet is meant to keep a record of all stock, as stock comes in/goes out it gets updated on the sheet however the issue i have is every row has a macro for in and a macro for out therefore each product has 2 macros (i'm sure you can understand how time consuming it is recording 2 macros every time new products are added).

The way it work is you type the quantity (in the 'In' or 'Out' cell) near the top of the sheet and then click the macro button (Green for In and Red for Out) and it recalculates the QTY in column F

Change Log Tab
This is meant to keep a record of every change made on the sheet, it records the time, product and the quantity in/out.
This is updated as part of the macros mentioned above (green/red buttons)


Can anyone advise the best way to achieve this without creating a ton of macros all the time?
If we have to start a new sheet all over again but meaning it would be a lot more streamlined then i am happy to do so

Regards,
Jamie

[JDobbsy1987]

Sorry to bump, just wondering if anyone can assist with this.

[jaslake]

Hi Jamie

Why don't you explain in words what it is what you wish to do. It's not readily apparent from your Code nor the description you've given. I have a clue as to what you want but I'd rather you detail your process and what you need rather than me guessing.

[JDobbsy1987]

Sorry if my initial post wasn't clear.
Basically, The sheet i attached to the original post has a load of macros on it (2 assigned to each row [1 for in stock and 1 for out stock])

When you click the macro it will recalculate the QTY in column F to show how much stock we have in.

Example
Type 10 into cell D5 and then click the button in D7 and it would update the QTY in cell F7 to show another 10 has been added.
It will also add information to the 'Change Log' tab and data sort it by latest date/time first (this is just to show a history of in/out stock)

The issue is, these are all manually recorded macros so every time a new product is added we have to record 2 macros for that product and we have hundreds of lines (more being added all the time).

As much as i like the simplicity of using this sheet i would like to remove the aspect of recording new macros all the time, is there a way to have some kind of master macro for in/out where you would type a qty in and click a button on that line (something like that... i'm open to options as im not sure whats the best way to do this.).

I know i already have buttons on each line but these are individual recorded macros.

I hope this helps?
Please advise if i can provide any further info or re-clarify anything?

Thank you very much.

Regards,
Jamie

[jaslake]

Hi Jamie

I'll not be spending much more time on this tonight...a bit late here. This is a big help...knowing that one must enter a quantity in Cell D5 and then clicking on Cell D7 to update Cell F7.

Knowing that, what should happen in Audit Sheet...there appears to be no changes with the above steps...

[JDobbsy1987]

No problems, its 02:24 in the UK so i need to go to bed

The audit sheet was a bit of work in progress, the idea around that is to generate a stock count effectively, this way we can print current products/qty's and do a stock check from time to time.

Once again, thank you for your help.

Regards,
Jamie

[jaslake]

Hi Jamie

This Code is in the attached. When a new Product is added simply click Add Buttons. Play with it...let me know of issues.

Please Login or Register  to view this content.

[JDobbsy1987]

That's awesome, thank you very much!

1 thing for the stock control, is it possible to change the change log so every time it is updated it puts it in order by date (latest date at the top, oldest date at the bottom), it's not an issue if that's not possible or don't worry if it takes a lot of work.

The stock control works perfect though

Cheers,
Jamie

[jaslake]

Hi Jamie

Sorry, forgot about the Sort. Not a problem...get back to you tonight.

[JDobbsy1987]

That would be great, are you able to help with the audit sheet?

I don't want to push my luck or take up any more of your time so i wouldn't be upset if you told me to go away

Audit Sheet
1. Generating it - When clicking the 'generate audit sheet' button it would create the inventory count in the audit tab.
2. Clear Audit Sheet - On the audit tab, when i click the clear audit sheet it would remove everything on there
3. Print - when clicking print it would print the audit sheet (not sure if this is possible though)

Cheers,
Jamie

[jaslake]

Hi Jamie

Replace the DoIns and DoOuts Code with this

Please Login or Register  to view this content.

Regarding this...

are you able to help with the audit sheet

It should probably be the Subject of a New Thread. In the New Thread please spell out what it is you wish to do. Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

If you wish, PM me with a link to your New Thread...I'll look at it.

[JDobbsy1987]

No problem, thank you very much for your help, that code has got the sort working perfect.

Regards,
Jamie

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.