Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
    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)

My situation:

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


share|improve this question
up vote 0 down vote accepted

This is really a question for Stack Overflow, but...

For question 1, I highly recommend using ADO. It's clean, it's fast, allows you to pass SQL requests, and doesn't require you to open a file to pull its data.

A good instructional list is located here:

It may initially look complicated, but it's far more simple than the alternative.

For question 2, if you're trying to link these cells, I suggest you avoid it. Rows and columns can be inserted when one workbook is closed, and your reference will be completely wrong - and the worst part: you won't even realize it because it will likely still pull data that looks correct, depending on your data set.

If you go with ADO, you can build in a function to auto refresh from the source workbook each time the summary book is opened (Workbook_Open in the ThisWorkbook object in VBA editor) or through a button on the sheet itself.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .