I have a worksheet that I use to keep track of some inventory. I enter products onto the sheet when I receive them and I have a column for quantity so I can see how many of each product I have. When I sell a product, I keep track of that on the same sheet. I do this by subtracting the sold product from the quantity and then recording that it was sold on the same row (this record includes a customer name, and sales tax information). When I have a product that has a large quantity, like 50, if I sell 2 of that product to 25 different people, I end up duplicating that row of the table 25 times and recording the sale to each customer with their sales tax info on each row. This happens over the period of a month at a time, so I only add a few transactions each day, not all 25 at once. I know there may be better ways to manage my inventory, but that is a subject for another day.
So the question is this. I want to make a macro in excel that will do the following. I place the cursor on the row of the product I am selling and click on the cell in the first column. When I run the macro, it should create a blank row above the active cell. Then copy several cells from the row I started on to the new blank row just above. The quantity on the original row should be changed to 0 and then the active cell should be placed on the quantity of the new row.
From there I would manually change the quantity to the right number and finish the row by adding the new sale and customer sales tax information.
On my desktop computer, I have a keyboard that records macros and I can accomplish this task easily. I am trying to move over to a notebook and cannot make this work properly. I have tried recording a macro with relative reference and the problem seems to occur with creating the new blank row.
If anyone can help out with this I would appreciate it.