Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
    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

Mike.

share|improve this question
add comment

1 Answer 1

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: http://www.xtremevbtalk.com/showthread.php?t=217783

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.