1. How can I compare two matching lists from separate workbooks? And then retrieve the data from the same row but another column - (Index&Match maybe be appropriate for this). But how does this look when put together? Can it be used in a macro?
2. How do you call/ask for the cell reference of the data to be inserted rather than the data itself? (drive/folder/workbook/worksheet:CellReference)
I have a master workbook (Master). Master is huge. It has numerous yearly sheets (2001, 2002, 2003, up to 2010). Each one has the same list of categories in COLUMN A:A. The data in each sheet is found in COLUMNS B, C, D & E.
My job is to get data from MASTER into a COPY workbook. COPY will have one worksheet that has the same categories list in COLUMN A:A.
The COPY worksheet will group the relevant yearly worksheets data - all data from the Column B's will be grouped into adjacent columns; and then all the data from column C's will be grouped in the next bacth of columns; etc...
Data in the Master can change so I can’t just copy&paste MASTER info, I need to link to it.
Oh, and I may have to do this exercise for over 30 workbooks…that’s why I’m interested in working with Macros, so that I can run (amend) when required.
As always, many thanks for any tips, links or guidance