Copy data from multiple sheets to one main based on cell value

**jdjenterprises** · 05-28-2013, 06:32 PM

I am brand new to the world of macros. I work for an automotive dealer. We want to take the attached spreadsheet, and if the value under "progress" says "Sold" I want that row to be copied to the sheet "Main", but keeping the numbering system in tact in Column A. I hope this makes sense. I've been playing with this for about 2 weeks.

Thanks
Jason Mitchell
Classic Beaumont
409-924-3417

**Pete_UK** · 05-28-2013, 08:55 PM

This thread shows how you can do that kind of thing using formulae:

http://www.excelforum.com/excel-form...xed-value.html

Hope this helps.

Pete

**jdjenterprises** · 05-28-2013, 09:01 PM

We have it setup the way it is in the original, as we need to keep up with each auction independently. Only a few vehicles sell at the given auction. It is a cool looking spreadsheet, but it is not functional for all purposes that I require. Thanks for help.

**p24leclerc** · 05-28-2013, 09:17 PM

Do you delete the row where Sold is present from that origin sheet? If not, your macro have to look up for duplicate before copying.

Where, in Main, should the row be copied? First empty row? Do you keep the original numbering in column A?
Also, what do you mean by keeping the numbering intact in column A? Is it that when a row is deleted, the numbers should be reassigned from 1 to xxx?

**jdjenterprises** · 05-28-2013, 09:20 PM

I do not want to delete the row from the origin page. it should copy to the next available row in the main. i would like them to be numbered in main 1-whatever..Thanks for the response.

Jason

**p24leclerc** · 05-28-2013, 09:49 PM

See attached file. You have to run the macro called Sold_Transfer. You can click on the Data menu and then on the Macros menu on the far right of the menubar. Then select the macro name and click on Execute.
Here is the macro:

Please Login or Register  to view this content.

**jdjenterprises** · 05-28-2013, 10:02 PM

That is exactly what I was looking for. YOu are friggin awesome. One last question...is it possible to make this automated versus having to run the macro each time?

**p24leclerc** · 05-28-2013, 10:13 PM

Here is the file modified to run when you open the workbook. Note that the macro was copied to the "ThisWorkbook" part of the VBEditor. and assing to the "OPEN" event.
The macro is still available to be run manually in the module.

Be advise that there are no test done on DUPLICATES. If you open this file each day and you do not erase the Sold cars in the various sheets, they will all be copied again into the Main sheet.
If you run this only once a month, then no problem.