Hi
I hope you can help me, I am new to VBA and really struggling to get my head round it, not a programmer. This might be a long post, but I really want to be clear. I am trying to copy cells from one worksheet to another based on the value in a cell
I have a workbook with 3 worksheets named Form, Data, Lookup. I have managed to create a form on the first worksheet, with some entry cells and a button. When I type text to the fields and click on the button, the VBA macro I am using copies the text from that sheet into the next sheet - Data.
In Data, row 1 are Headers, row 2 is the text from the Form sheet, so A1 = Item Number, A2 = text (for this example, text=Item1). I am also populating column B from the form, so B1 = Quantity, B2 is a number. I also have Headers in columns C, D and E, C = Part No, D = Cost and E = Total.
In the Lookup worksheet, I have data I have entered already. Again, row 1 has headers, all other rows hold data. So headers - A1 = Item Number, B1 = Part No, C1 = Cost.
So, for my example - A2 = Item1, B2 = PartNo1, C2 = 1.23, then row 3 - A3 = Blank, B3 = PartNo2, C3 = 2.40. Row 4 = another part for Item1, so A4 is blank. Item1 has only 3 parts so row 5 has Item2 in cell A5, and then the rows of parts for Item2.
So the whole worksheet lists all the parts associated with all the items, and column A only has entries when the Item number changes. Items don't have the same number of parts, in my example Item1 had 3 parts, Item2 may have 10, Item 3 only 1.
So - what I am trying to do. Someone fills in the form, clicks on the button and a row with Item Number and Quantity gets added to worksheet Data. I then need columns C, D filled in automatically with the correct number of rows for that item from worksheet and column E calculated by multiplying cost by quantity.
I have the form working, but getting a varied number of rows based on the Item Number is eluding me
Any help gratefully received
I hope you can help me, I am new to VBA and really struggling to get my head round it, not a programmer. This might be a long post, but I really want to be clear. I am trying to copy cells from one worksheet to another based on the value in a cell
I have a workbook with 3 worksheets named Form, Data, Lookup. I have managed to create a form on the first worksheet, with some entry cells and a button. When I type text to the fields and click on the button, the VBA macro I am using copies the text from that sheet into the next sheet - Data.
In Data, row 1 are Headers, row 2 is the text from the Form sheet, so A1 = Item Number, A2 = text (for this example, text=Item1). I am also populating column B from the form, so B1 = Quantity, B2 is a number. I also have Headers in columns C, D and E, C = Part No, D = Cost and E = Total.
In the Lookup worksheet, I have data I have entered already. Again, row 1 has headers, all other rows hold data. So headers - A1 = Item Number, B1 = Part No, C1 = Cost.
So, for my example - A2 = Item1, B2 = PartNo1, C2 = 1.23, then row 3 - A3 = Blank, B3 = PartNo2, C3 = 2.40. Row 4 = another part for Item1, so A4 is blank. Item1 has only 3 parts so row 5 has Item2 in cell A5, and then the rows of parts for Item2.
So the whole worksheet lists all the parts associated with all the items, and column A only has entries when the Item number changes. Items don't have the same number of parts, in my example Item1 had 3 parts, Item2 may have 10, Item 3 only 1.
So - what I am trying to do. Someone fills in the form, clicks on the button and a row with Item Number and Quantity gets added to worksheet Data. I then need columns C, D filled in automatically with the correct number of rows for that item from worksheet and column E calculated by multiplying cost by quantity.
I have the form working, but getting a varied number of rows based on the Item Number is eluding me
Any help gratefully received
