Consolidating multiple worksheets into one and retain links to source data in master sheet

Hi everyone,

I have 50+ spreadsheets, with the same column headings and # of columns, as well as the tab names (i.e., *Report* is the commonality). Every week after analyzing a particular report, I name that tab "[insert week] Report." Afterwards, I want to run a code that consolidates ALL of my tabs with the *Report* into one single spreadsheet. However, rather than showing the data as hard coded values when running the VBA Code, I'd like to show the source data too. So for example, my first report is dated 1/1/2011, which means on the consolidated master sheet, the 1/1/2011 values should be "='1-1-2011 Report'!Cell Position" in the formula bar, instead of showing the paste special values. This way I can evaluate what changes I need to make in the consolidated master sheet and be able to click on that cell directly to make the change. If I need to make a change to the 1/1/2011 tab, then the consolidated master sheet will be automatically updated to reflect the change.

See below for a VBA code that I found that does part of what I'm looking for. I've already modified the data ranges in the code based on what I want the master tab to copy and paste from. The issue with this code is it only copies and paste special values when the code is generated. I don't have insight on the source data of the values on the consolidated master sheet.

Any suggestion is greatly appreciated! Thanks so much!

Please Login or Register  to view this content.

Moderator Edit:

Welcome to the forum.

Please notice that code tags have been added to your post. The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at the top of the page and read Rule #3.
Thanks.

The PasteLink method only seems to work on the Activesheet. So you want to ensure you stay on the Consolidated sheet the whole time. Give this a try:

Please Login or Register  to view this content.

Wow- that worked!! Thank you!!! I would like to make two other additions to the code if possible:
1. In cases where the individual report tabs have blank values in a column, is there a way to embed a formula in the VBA code so the values aren't showing "0" as the values? For example, I have a column titled "Comments" but sometimes there aren't any comments annotated in this column. So before I found this VBA code, I would manually enter in the formula as =TEXT('1-1-2011 Report'!N19,"") which will give me blank values as text if there's nothing in the cell. However, there are character count limitations with this code. If I have 255 characters in the original source cell, running this TEXT formula would yield #VALUE! results, which is not what I want. Based on your edited VBA code, I see values of "0" where the original source data is blank. Can we somehow eliminate the "0" and leave it as blank when the code is generated? The "0" output looks slightly confusing and not pleasing on the eyes. If there is a way to include this criterion in the VBA code, can we eliminate the character count restriction?

2. Going hand in hand with the Comments column, I have a date column that shows when the comments were made. Again similar to the example above, sometimes there are no comments and thus no dates are entered. So we would see blank values in those 2 columns on the individual sheets. However when I ran your VBA code, I get "1/0/1900" as the values for the blank ones. Prior to the usage of VBA, I would enter in =IF(TEXT('1-1-2011 Report'!V19,"mM/dd/yyyy")="01/00/1900","",TEXT('1-1-2011 Report'!V19,"mM/dd/yyyy")) as the formula. Is there a way to modify the current VBA that automatically changes blank dates into blank values, rather than 1/0/1900?

Again- thank you for the input!

1) While the Consolidated sheet is onscreen, go into the File > Options > Advanced > Sheet options and disable Zero values, cells with a value of zero will show blank instead on that sheet.

http://screencast.com/t/X25ncyNc5V3

2) Does #1 fix that?

I have marked this thread solved for you.
In the future please select Thread Tools from the menu above and mark the thread as solved. Thanks.