I would like to copy selected records from one sheet to another

**mechant** · 10-31-2008, 08:30 AM

Hi guys,

I have a sheet with the pricelist and a sheet with the actual price offer. I would like to copy selected Items from the price list to the price offer. I dont want to do that by copy and paste method. I would like to create check boxes, so that I can check / uncheck each Item I want to be copied and then press the button copy to the price offer ... how can I do that. I asssume I would need a macro for that. The columns in the pricelist and the price offer sheet will be identical of course,

**royUK** · 10-31-2008, 08:56 AM

Can you use Data validation list & VLOOKUP? Attach an example workbook.

**mechant** · 10-31-2008, 09:16 AM

Well, I will send the database in MS2003 format. What do I want to do?

I want to create checkboxes, or somehow mark the records from the pricelist. e.g.
Product B, Product D and Product E and move them to Priceoffer sheet ..
The best thing for me would be to add a macro button next to each row which says :add to the price offer. and also I would like to have a macro button that will add a new record ( a new row) in the pricelist . I send the file in the attachment

**royUK** · 10-31-2008, 09:36 AM

You don't need VBA. Just Data validation & VLOOKUP. See

http://www.excel-it.com/data_validation.htm
http://www.excel-it.com/excel_functions.htm

I have added two Dynamic named Ranges to the data sheet

item_ids provides the drop down list to pick from in Column A of price offers
data is the actual price list referred to in the VLOOKUP

**mechant** · 10-31-2008, 10:08 AM

Well, that is a great Idea... and after I saw your example, I would like to create a dropdown list in the price offer sheet ... so I could select a product name from the dropdown menu and it would automatically fill in the weight and the price by the VLOOKUP function. But I cannot use the validation ( list) to create a dropdown menu, because it says that I cannot use data for validation from another worksheet. How can I then make a dropdown menu ( name in the price offers table) and it will take the data from the name column in the pricelist table)? Is it possible to create a search filter in the dropdown menu? so that the user types a first few leters and the dropdown will narrow down to the names containing only those first few letters?

**mechant** · 10-31-2008, 10:32 AM

Well, I have managed to create the dropdown menu by using the dynamic range name as a source , but when I create the Vlookup and use the dropdown menu as the lookup value, then I select the table on the pricelist as my table array, select the weight column as the column_index number and add false for the exact match . I get the N/A value as the result. I send what I have done in the attachment . also I would like to remove name from the dropdown, but It does not accept when I define the range as $B2:$B ... that means that I want as a range the whole B column except from B1, because I do not know how many records I will have in the pricelist