trying to copy multiple rows from one worksheet to another based on cell value

beckyr

New Member
Joined
Mar 23, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

G0dsreaper

New Member
Joined
Jan 14, 2021
Messages
6
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Your post has lot to take in. Could you post a couple of pictures with sample data in them to help visualize what you are trying to accomplish.
 
Solution

beckyr

New Member
Joined
Mar 23, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Your post has lot to take in. Could you post a couple of pictures with sample data in them to help visualize what you are trying to accomplish.
Hi, thank you so much for replying. I was going to close my post down today, because I gave up on Excel and VBA yesterday and decided to do everything I was trying to do in Access. I am way more a SQL scripting person than a VBA person, so I decided to work with my strengths. But, again thank you to everyone who read my post and especially, thank you G0dsreaper for replying.
 

G0dsreaper

New Member
Joined
Jan 14, 2021
Messages
6
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
That is funny because I am the exact opposite. I don't know anything about SQL and am just learning VBA. Good luck to you though.
 

beckyr

New Member
Joined
Mar 23, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
That is funny because I am the exact opposite. I don't know anything about SQL and am just learning VBA. Good luck to you though.
Thanks - and good luck to you with VBA!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,335
Messages
5,641,557
Members
417,220
Latest member
lam150498

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top