Consolidate Sheets VBA

[10190879]

Hi All,

I have the attached excel file running dummy data. It runs a macro to consolidate the 3 sheets ('Olson Opportunities' , 'Ortenzio Opportunties' , and 'Sherman Opportunities') into 1 sheet called 'Matts Team Opportunities' (when the Refresh Data button is clicked on the 'Team Opportunity List' sheet).

I then have a linked sheet to the data on 'Matts Team Opportunities' called 'Team Opportunity List' which I am planning on adding a few more fields to in order to do reporting off of. The issue I am running into is as follows:

When I refresh the data - I get a bunch of #REF! errors and only the first 5 rows show up with data. I need to be able to do dashboard reporting off of the consolidated data - but the way it stands now, I am unable to do that.

Any help would be greatly appreciated!

Best,
Nick

[AB33]

You have linked formula. This is like a cut function. When you cut a cell with formula, you get this error.
This is normal in excel. You are overwriting with no values, hence no reference. If you copied them again manually, the error disappears.

[10190879]

Thanks, AB33. However, I can't copy them manually every time. This workbook is going to be linked to others in a SharePoint site. I need the data to automatically update for reporting purposes to senior management (there will be a dashboard and multiple reports running off of this data). Any suggestions there?

[AB33]

Why are you copying the same information to different sheet? Why can not you use these as final consolidation where the data are copied?

OR run another separate code where this code copies from the consolidates sheet in to final sheet. This will avoid the error.

[10190879]

I cannot get that data to refresh in dashboards and reports, it stays stagnant when refreshed.

The dashboard would show such data as 30, 60, 90 days pipeline, by rep, by team.

Close Won/Los deals
Win Rates

Top 10 deals in pipeline

And I cannot accomplish that currently using the consolidated data tab.

[AB33]

Why is the difference between these two sheets, except one is a table and other is a range? As I said, if this is the only option you have, run another code after end of the main(This) code just to copy the data only

[GeneralDisarray]

Your are getting #REF error specifically because of this line:

Please Login or Register  to view this content.

That will happen whenever you .delete a referenced range. Why not just clear the contents?

Please Login or Register  to view this content.

[GeneralDisarray]

TRY THIS:

The Zeros are Filtered from the final presentation sheet (if that does not work for your project purposes, I would suggest clearing them from each sheet PRIOR to copying to the main sheet).

Please Login or Register  to view this content.

[10190879]

Thank you, GeneralDisarray - I don't know why I didn't think about that... the simple things!

Unfortunately, after altering the code - the refreshed data now only hows the first 5 rows of data (i.e. Olson1.1 - Olson 1.5) - the other data is now missing. What would be the reason for that? I don't understand.

[GeneralDisarray]

Which sheets are you trying to run on? The hidden sheets ending in _Data?

This SHOULD work - if it don't, i'll see what I can do after lunch

Please Login or Register  to view this content.

[10190879]

Thank you, GeneralDisarray - you killed it man. this works perfectly for what I need. I appreciate the help here - really saved me some hours of headbanging into my desk. Take the rest of the day off, you deserve it, lol!

Cheers

[GeneralDisarray]

sweet, glad to hear it worked thank you for the rep