Delete unneeded text in cells while keeping relevant text, then exporting that information

[shrekspeare]

Good afternoon!

I have an inventory sheet that I receive once a month. In each cell in Column B, there is a product number which varies in length and formatting, the name of a product which varies in length and formatting, and then purchasing specific information. I'm looking for a code that will:


1. Identify numbers greater than zero in Column Z (amount of product used for the month)
2. Take the row attached to the greater than zero numbers in Column Z and delete the extra text in the Column B cell to leave only the product number
3. Export only the product number from Column B and usage from Column Z into a brand new workbook to fill column D (productid) with the product name and F (Usage) with the column Z usage numbers

There are no formulas in the cells and the data isn't linked to anything. It's simply transcribed on the excel sheet by another person from a handwritten sheet they receive.

I have separate code I use for another file that searches data in Sheet2 for keywords specified in Sheet1, then creates Sheet3 and exports that information. Can I tweak the code I already have for what I need or is that making too much work for myself? What's the simplest way to do this?

Bonus points if you can tell me how to tweak the code so the new workbook columns A, B, C, E, G, H, I, and J prefill with standard information such as locations and dates.

 

[alansidman]

Using XL2BB, post a sample work sheet of 8-10 records. Then mock up your expected solution for those same records and post with XL2BB