jonsharman
New Member
- Joined
- Jan 4, 2014
- Messages
- 28
Hi
I would like to see if something is possible before venturing into too much detail and if anyone can give me a steer on how this might work?
I currently have an Order / Profit Workbook that updates the Master Stock file at either the click of a button or when it is more than 24 hours out of date. The stock file is exported by a proprietary account system overnight into *.xlsx format and when the VBA code is triggered by either of the conditions above the macro copies the proprietary stock export sheet into the main workbook, deletes all the data from the Stock sheet within the Order / Profit workbook, copies the whole new stock from the export file onto the Stock sheet and then deletes the sheet that is no longer required.
The issue I have here is that if an item of stock becomes obsolete or goes out of stock it is deleted from the system overnight! When the routine runs it therefore deletes any trace of this item as having been in stock which causes a problem when you recall a quote in the Workbook as the row that contains the data in the Stock sheet has now been deleted. My first thought was just to keep adding the stock to the bottom of the Stock sheet but this of course results in duplicate stock records and doesn't allow for changes in the cost price, discount codes, retail price etc. meaning I end up having the same item in stock several times but with inconsistent pricing data so the quotes become inaccurate.
Is it possible to:
If anyone has any guidance or can offer some code for me to start with as this one has got me stumped I would be very grateful!
Thanks
I would like to see if something is possible before venturing into too much detail and if anyone can give me a steer on how this might work?
I currently have an Order / Profit Workbook that updates the Master Stock file at either the click of a button or when it is more than 24 hours out of date. The stock file is exported by a proprietary account system overnight into *.xlsx format and when the VBA code is triggered by either of the conditions above the macro copies the proprietary stock export sheet into the main workbook, deletes all the data from the Stock sheet within the Order / Profit workbook, copies the whole new stock from the export file onto the Stock sheet and then deletes the sheet that is no longer required.
The issue I have here is that if an item of stock becomes obsolete or goes out of stock it is deleted from the system overnight! When the routine runs it therefore deletes any trace of this item as having been in stock which causes a problem when you recall a quote in the Workbook as the row that contains the data in the Stock sheet has now been deleted. My first thought was just to keep adding the stock to the bottom of the Stock sheet but this of course results in duplicate stock records and doesn't allow for changes in the cost price, discount codes, retail price etc. meaning I end up having the same item in stock several times but with inconsistent pricing data so the quotes become inaccurate.
Is it possible to:
- Copy in the 'Import' sheet into the Workbook (this bit already works)
- Compare the 'Import' against the 'Stock' sheet
- If the unique identifier in Column G exists in both the 'Import' and 'Stock' sheets then check the data for that row in columns A - D and J - V and, if the data in that row in the 'Stock' sheet is inconsistent with the data in the 'Import' sheet then update 'Stock' sheet from the 'Import' sheet.
- If the unique identifier in Column G exists in the 'Import' sheet but not the 'Stock' sheet then add the data to the next clear row at the bottom of the 'Stock' sheet
- If the unique identifier in Column G exists in both the 'Import' and 'Stock' sheets and the data in all the columns for that row are consistent then do nothing.
If anyone has any guidance or can offer some code for me to start with as this one has got me stumped I would be very grateful!
Thanks