Consolidation of multiple sheets to one sheet

[aparnawangu]

Hi Team,

I have a set of excel sheets containing capacity data of resources client wise.I need to consolidate all the data in a single sheet .Attaching the excel file. Pls guide me how to do this with the help of VBA code.There are total 14 sheets which needs to be consolidated in the consolidation file.Pls help as I am a newbie.


Regards,
APS

[davesexcel]

So far you only have data in the columns from E to I so the code will just loop through those columns

Please Login or Register  to view this content.

[aparnawangu]

Hi,
I need consolidation for all the sheets including client services sheet in the end.Currently its not including that sheet values.Please include that sheet also in the code.

Thanks
APS

[davesexcel]

You will have to edit your workbook to accommodate the code. All sheets need to be the same, that's why I excluded that sheet in the code.

Why would you have all the sheets the same except for 1?

[aparnawangu]

Hi
As there were different set of client projects
In client services section so I had to accommodate all the
Clients in the same sheet one after the other.If I would not have done that then I would end up creating 10 to 12 more client services sheets.pls suggest how to include clients services sheet values also in the consolidation sheet

Regards
aps

[aparnawangu]

Hi Friends,

Can anybody help me on the above query.I know that the format for all the sheets should be same for Macro to work but I am not able to think through the format of client services sheet.Rest all the sheets have same format.So if anyone can help me with the format of Client Services sheet I will be highly greatful also need to include the code for the sheet in macro.

Regards
Aps

[davesexcel]

Try this,

Please Login or Register  to view this content.

[aparnawangu]

Hi
Thakyou so much for the help
Just one query I have included one extra column in the consolidated sheet only called portfolio so in this scenario the range will change as the current range was E4:I4 but now as there is an extra column in consolidated sheet so the months will start from F4:J4.Please suggest how to specify the range now.

Thanks
APS

[davesexcel]

It's already adjusted in the code, did you try it?

[aparnawangu]

Hi
Everything is working perfectly.Thanks a lot
But what is happening now that Nov data is getting pasted in the new portfolio column as there is one extra column in only consolidated sheet.Thats y I was asking for the range which should be taken now.Pls suggest

Regards
Aparna

[jindon]

See if this is how you wanted.

Please Login or Register  to view this content.

[aparnawangu]

Hi Davesexcel,

Have you seen my query?Please help me with this query.For your reference I added one more column called
Portfolio so the month range has shifted one column ahead from E to F now.Please help me change the range in the code


Regards
Aps

[davesexcel]

I did see the post, I don't know what you did and you haven't supplied a new sample workbook.

I also noticed you did not reply to @Jindon's post.

[aparnawangu]

Hi Friends,

First of all apologies for not replying to Jindon's post but I was workingon that code as well .As there was a change in the total no of columns in the Consolidated sheet so the result was not coming properly.The nov data was getting pasted in the new column named Portfolio.

Please suggest the new range which needs to be used.I will be highly greatfull

Regards
Aps

[davesexcel]

Here you go.

Please Login or Register  to view this content.

[aparnawangu]

Hi Friends,

Yes the final sheet is consolidation and for consolidation the data will have to be taken from all the sheets till Client Services.I need the total resourcewise and for all the clients which are mentioned sheetwise.

Please let me know incase any other info is required.

Regards
Aps

[davesexcel]

I changed it to use a sumif function instead

Please Login or Register  to view this content.

[aparnawangu]

Hi Dave,

It worked!! finallyThanks so much for the support.

Only some of the values are not matching in between randomly.I will try to figure out the problem.If not able to then I will seek ur help.

Regards
Aps

[Winon]

Hello aparnawangu,

Only some of the values are not matching in between randomly.

I find that hard to believe. Please could you give us an example of same

Regards.

[Winon]

Hello aparnawangu,

In the meanwhile, please try davesexcel Code, which I have tweaked a bit, as below. It runs considerably faster

Please Login or Register  to view this content.

Regards.

[aparnawangu]

Hi Winon,
Yes the problem is sorted now and the code is working perfectly ☺

[davesexcel]

Hello aparnawangu,

In the meanwhile, please try davesexcel Code, which I have tweaked a bit, as below. It runs considerably faster

...........

Thanks Winon,
If you look at Column F in my attached example, you will see an Indirect formula That would work in Column G.

If you could come up with an Idea that would change the columns in the formula into Column G to X, then the results would be instantaneous.

You will see I have a range of cells in Column Z that is named "Sheet" that is used in the sumproduct formula.

IndirectPic.jpg

[Winon]

Hello aparnawangu,

Thank you for the positive feedback.

All credit goes to davesexcel though, and I truly believe you should award him with an "Add Reputation" by clicking on the Star to the Left at the bottom of his Post.

Regards.

[Winon]

@ davesexcel,

The Dim x in my tweak renders your formula and the Sheet Names in Column Z not worthy of displaying on the Sheet itself. Your brilliant lateral thinking in your Code, now takes Care of everything automatically.

As you have quite correctly mentioned;

...the columns in the formula into Column G to X, then the results would be instantaneous.

Please see the attached, and comment out the Calculation Lines, and you won't notice any difference!

Kind regards my Friend.

W.

[Winon]

@ davesexcel,

Seems That I was waffling a lot of bullSh$t.

I have taken your Latest Workbook and deleted the Formulas in Columns F and Z and your Formulas on the Sheet. Results are still instantaneous.

Good work my man!

Cheers.

[davesexcel]

Here's an answer to my query in Post #22,if somebody has time to put a timer on it, it would be interesting to see the times for all codes.

Please Login or Register  to view this content.

[Winon]

@ davesexcel,

In Post # 14 the OP shows how he/she wants Column F to look like, which your solution does not reflect.

Regards.

[davesexcel]

Here is a timer using both codes.
Interesting,
with screenupdating set to true, the sumproduct code take 1 second, and 4 seconds for the sheet looping code.
with screenupdating set to false the sumproduct code still takes 1 second but the sheet loop is less than 1 second.

See attached, please note the named range in column z that has the sheet names

[davesexcel]

I see the possible confusion,
There was a new attachment for this question located here.
https://www.excelforum.com/excel-pro...ml#post4804274

[Winon]

Thank you Dave,

To keep my sanity, I think I shall now gracefully retire from participating in this confusing duplicate issues with contradicting requirements.

Keep up your good work!

Kind Regards,

[aparnawangu]

Hi Friends,

Happy New Year to All.

The consolidation macro worked perfectly.
There is an additional requirement in the same file which I will be working on.The req is like in some projects there are some resources whose names are not confirmed but the capacity forecast can be done according to roles.So for that also some line items are included in some projects.
Now I have to consolidate thet data as well in the same consolidation sheet.Please refer attached file.Please guide me that how can I achieve the desired result.

Thanks Aps