Copy multiple cells based on another cell's value

Hello!

I need help with multiple linked values and their sorting, copying..

I have one workbook with values like this (informative only)
A | B | C | D | E | F | G
ID No. | Height | Weight | Age | Color | Rating | Price

ID number is the most important value, based on that value all other values are entered manually and that's ok. Id number grows as rows add (row 2= ID 252, row 3 = ID 253, etc.).

I need to copy those values to another workbook but in different order. ID numbers are already in the other workbook, I just need to copy other values connected to ID number in order like this:

A | B | C | D | E | F | G
ID No. | Rating | Price | Color | Weight | Height | Age

So basically I'm constantly adding, copying values to Workbook 2 manually..
Can it be done more time saving and how?

I was thinking of a macro button next to each ID Number which will copy values in corresponding columns into the other woorkbook in different order. Or, can that be automatic? With one button press?
I really don't know how to do that. I want to get rid of manually copying of every value to another workbook, so I need advice..

Also, since those workbooks have large amount of data, would it be better to store them in access. I'm pretty newbie to this so any advice, guideance will be helpful..

Thanks in advance!

Vedran C.

You can look up data from a "database" (your main workbook) from your other workbook with simple lookup formulas. As long as the matching ID is in column A, you can use a VLOOKUP().

In workbook 2 to get the RATING for the ID listed in cell A2 on that sheet, put this formula in B2:

=VLOOKUP($A2, '[Book1.xls]Sheet1'!$A:$F, 5, 0)

Just change the red number for the column you want to return a value from.

Height = 2
Weight = 3
...etc.

Of course you'll need to edit the workbook name.

1) You can use Conditional Formatting to change the appearance of the cells with #N/A in them, perhaps change the color of the font so that it's invisible?

2) You can remove formulas anytime you want by:

a) highlighting a range of cells
b) Ctrl-C to copy them
c) Click on Edit > Paste Special > Values

3) Access is a better database, of course. You would have to pose Access questions to Access forums/users.

'SHEET1 TO MANY WORKBOOKS

I have a macro that may be "ready to use" for parsing rows of data from one sheet to many workbooks named for the same values in that evaluation column.

Sheet1 to Many Workbooks

My macro names the workbooks for values in the column PLUS today's date, you can take a stab at removing the date part...or leave it in, it's a good technique.


There are many various macros on the site for parsing data several ways, the code is meant to teach since often you need parts of one macro and parts of another to cover your specific needs.

The Variable names I use are meant to guide you. SvPath = Save Path...the folder on your hard drive where the files will go.

Edit that code highlighted in red so that it points to your own proper save path, or create a 2010 folder on your C:\ drive.

My macro doesn't take into account any sort of buttons or whatnot. I can't comment on that blindly.

Not knowing what changes you've made, I'll reference the original code. If you look at the copy / paste commands you could probably spot the line that is pasting ALL instead of pasting VALUES.

'Loop through list one value at a time
    For Itm = 1 To UBound(MyArr)
        ws.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=MyArr(Itm)
        
        ws.Range("A1:A" & LR).EntireRow.Copy
        Workbooks.Add
        Range("A1").PasteSpecial xlPasteValues
        Range("A1").PasteSpecial xlPasteFormats
        Cells.Columns.AutoFit
        MyCount = MyCount + Range("A" & Rows.Count).End(xlUp).Row - 1
        
        ActiveWorkbook.SaveAs SvPath & MyArr(Itm) & Format(Date, " MM-DD-YY"), xlNormal
        ActiveWorkbook.Close False
        
        ws.Range(vTitles).AutoFilter Field:=vCol
    Next Itm

See it? I added another line directly below it to also paste in the cell formatting so that the appearance stays the same, too.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

1) For your other macro requests, not actually related to this thread, start a new thread.

2) For your desire to save in a different path, just edit the SvPath to where you DO want to save to, my "C:\2010\" was just meant to get you started.

3) I can't speak to Excel 2000 limitations, but I know of no reason the macro should stop halfway for any reason. That doesn't mean I don't believe you, just I can't help with earlier versions of Excel.

Nothing in the macro is particularly memory intense. It does most of the work directly on the worksheets themselves, so I know of no reasons low memory would effect it either.

4) You can use the link my signature to get my contact address from my website. I would be happy to look at the file you're actually using this on.