Referencing Cells in Multiple Worksheets

[RS]

We have several workbooks with greater than 100 worksheets and need to
reference several cells from each worksheet to either a new
worksheet/workbook to create a new table. The only thing I can think of is
linking, but this just doesn't seem like an efficient way to do this. Is
there a way that doesn't involve using Visual Basic or some type of
programming? I am assuming that programming is the only way that this can be
accomplished. Any input would be much appreciated!

 

[JLatham]

Just some food for thought:
Linking will work, of course. But the issue there is that the linked
workbooks either must be available or you would need to break the links at
some point. If those workbooks get moved, then you end up with broken links
to deal with. And if the contents of those other workbooks will not change
in the future, it's almost a waste to take up the system resources in keeping
the links.

There is one other way to do it without VBA: instead of linking to the other
workbooks and their sheets, you can REALLY get labor intensive and copy from
the other books/sheets and paste into the new workbook.

Other than linking or copy'n'paste, there's not a way to do it without
VBA/macro code. But a VBA solution offers some advantages:
once the job is done, you can delete the macro to shrink the size of the end
workbook slightly,
if you anticipate having to do this type of thing again in the future, you
can keep a copy of the code around to use at that future time and not have to
rewrite it.

The VBA code to do such a thing really isn't that difficult to come up with:
if you put all of the workbooks into a single folder for the task, it becomes
much easier, even if it's just copies of them for this stage of the project.
At that point, all that is needed to have is a 'map' of source data and that
data's destination. What that would be is a list of the worksheets in the
other workbooks and the cells on each that need to be copied, along with the
sheet name and destination cell addresses for the data. In some cases you
can 'shorthand' it, as:
source Sheet1 A1:B18 needs to go to dest. Sheet1 beginning at H1 - that
would tell the coder that 2 columns and 18 rows need to be copied and would
end up at H1:I18.

 

[RS]

I sincerely appreciate your input. It was very helpful. Thank you so much!
RS