populate data from multiple worksheets on to another worksheet

[habi01]

Hi, I have a workbook that contains multiple price sheets for different products. Each price sheet contains drop down menus for the respective product within that price sheet, including prices and sku numbers. The goal is: when someone chooses a product from the drop down menu on a price sheet within the workbook, the product name, sku #, quantity and price populates on a quote sheet within the same workbook. I want to create a macro that does this on the quote sheet. It would need to see that if the first row of the quote sheet is full to move to the next row down and fill in there. Can this be done?

 

[anupagarwal06]

I have used a similar feature in one of my older works. The following steps can help:


For drop down, you can use Data Validation with list, and use Vlookup or Index+Match to show Sku#, price & quantity.


Add buttons on each of price sheets to confirm an item and populate on the quotation sheet


Use CountA function (and adjustments line adding or subtracting some numbers) to determine the target row number. Then fill the target row with values.


I have tried to make a small demo and uploaded here:

https://skydrive.live.com/redir.aspx?cid=16d5e40738502388&resid=16D5E40738502388!103&authkey=xmIdtAa3YnU%24

 

[habi01]

I will try that, thank you so much for the help!

 

[habi01]

Hey your solution is awesome and helped me a lot. I used the code in your macro, I have just one question: I need the information to carry over on the quote page to cell A:17 and it starts in A:1, how do I fix that? Here is the code I used:


Sub quote()

Dim Row As Integer


Row = WorksheetFunction.CountA(Range("Quote!A17:A17")) + 1


Sheets("Quote").Cells(Row, 1).Value = Range("A3").Value

Sheets("Quote").Cells(Row, 2).Value = Range("B3").Value

Sheets("Quote").Cells(Row, 3).Value = Range("C3").Value

Sheets("Quote").Cells(Row, 4).Value = Range("D3").Value

Sheets("Quote").Cells(Row, 5).Value = Range("E3").Value


End Sub


Thank you!

 

[Luke M]

If you know it's going to start in A17, you can use this line:

[pre]

Row = 17
Rather than this

Row = WorksheetFunction.CountA(Range("Quote!A17:A17")) + 1

[/pre]
BTW, the problem is the Counta function is returning, at most 1, and if A17 is blank, 0.

 

[habi01]

Hi Everyone,


I need my VBA code to populate info from any of the product sheets in the workbook, which have multiple rows on them. Also, it will need to see when line 17 is full and then move down to the next open row. I have uploaded the spreadsheet for a better understanding of what I am trying to accomplish:


https://skydrive.live.com/?cid=e5dcca8e5ee473a4&sc=documents&id=E5DCCA8E5EE473A4%21103


Thank you!!

 

[Luke M]

I know this only answers part of your question, but this will tell the macro the correct row to select

[pre]

If Worksheets("Quote").Range("A17") <> "" Then
Row = Worksheets("Quote").Range("A65536").End(xlUp).Row + 1
Else
Row = 17
End If

[/pre]

 

[habi01]

Thanks Luke!