+ Reply to Thread
Results 1 to 31 of 31

Consolidation of multiple sheets to one sheet

  1. #1
    Registered User
    Join Date
    07-06-2015
    Location
    India
    MS-Off Ver
    PC 2016
    Posts
    87

    Consolidation of multiple sheets to one sheet

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,144

    Re: Consolidation of multiple sheets to one sheet

    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.

  3. #3
    Registered User
    Join Date
    07-06-2015
    Location
    India
    MS-Off Ver
    PC 2016
    Posts
    87

    Re: Consolidation of multiple sheets to one sheet

    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

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,144

    Re: Consolidation of multiple sheets to one sheet

    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?

  5. #5
    Registered User
    Join Date
    07-06-2015
    Location
    India
    MS-Off Ver
    PC 2016
    Posts
    87
    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

  6. #6
    Registered User
    Join Date
    07-06-2015
    Location
    India
    MS-Off Ver
    PC 2016
    Posts
    87

    Re: Consolidation of multiple sheets to one sheet

    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

  7. #7
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,144

    Re: Consolidation of multiple sheets to one sheet

    Try this,
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    07-06-2015
    Location
    India
    MS-Off Ver
    PC 2016
    Posts
    87

    Re: Consolidation of multiple sheets to one sheet

    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

  9. #9
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,144

    Re: Consolidation of multiple sheets to one sheet

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

  10. #10
    Registered User
    Join Date
    07-06-2015
    Location
    India
    MS-Off Ver
    PC 2016
    Posts
    87
    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

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,936

    Re: Consolidation of multiple sheets to one sheet

    See if this is how you wanted.
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    07-06-2015
    Location
    India
    MS-Off Ver
    PC 2016
    Posts
    87

    Re: Consolidation of multiple sheets to one sheet

    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

  13. #13
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,144

    Re: Consolidation of multiple sheets to one sheet

    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.

  14. #14
    Registered User
    Join Date
    07-06-2015
    Location
    India
    MS-Off Ver
    PC 2016
    Posts
    87

    Re: Consolidation of multiple sheets to one sheet

    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
    Attached Files Attached Files

  15. #15
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,144

    Re: Consolidation of multiple sheets to one sheet

    Here you go.


    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    07-06-2015
    Location
    India
    MS-Off Ver
    PC 2016
    Posts
    87

    Re: Consolidation of multiple sheets to one sheet

    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

  17. #17
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,144

    Re: Consolidation of multiple sheets to one sheet

    I changed it to use a sumif function instead

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by davesexcel; 12-17-2017 at 11:14 AM.

  18. #18
    Registered User
    Join Date
    07-06-2015
    Location
    India
    MS-Off Ver
    PC 2016
    Posts
    87

    Re: Consolidation of multiple sheets to one sheet

    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

  19. #19
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,100

    Re: Consolidation of multiple sheets to one sheet

    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.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  20. #20
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,100

    Re: Consolidation of multiple sheets to one sheet

    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.

  21. #21
    Registered User
    Join Date
    07-06-2015
    Location
    India
    MS-Off Ver
    PC 2016
    Posts
    87
    Hi Winon,
    Yes the problem is sorted now and the code is working perfectly ☺

  22. #22
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,144

    Re: Consolidation of multiple sheets to one sheet

    Quote Originally Posted by Winon View Post
    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

  23. #23
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,100

    Re: Consolidation of multiple sheets to one sheet

    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.

  24. #24
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,100

    Re: Consolidation of multiple sheets to one sheet

    @ 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.

  25. #25
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,100

    Re: Consolidation of multiple sheets to one sheet

    @ 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.
    Last edited by Winon; 12-18-2017 at 07:54 AM.

  26. #26
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,144

    Re: Consolidation of multiple sheets to one sheet

    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.
    Last edited by davesexcel; 12-18-2017 at 08:17 AM.

  27. #27
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,100

    Re: Consolidation of multiple sheets to one sheet

    @ davesexcel,

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

    Regards.

  28. #28
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,144

    Re: Consolidation of multiple sheets to one sheet

    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
    Attached Files Attached Files

  29. #29
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,144

    Re: Consolidation of multiple sheets to one sheet

    I see the possible confusion,
    There was a new attachment for this question located here.
    https://www.excelforum.com/excel-pro...ml#post4804274
    Last edited by davesexcel; 12-18-2017 at 09:33 AM.

  30. #30
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,100

    Re: Consolidation of multiple sheets to one sheet

    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,

  31. #31
    Registered User
    Join Date
    07-06-2015
    Location
    India
    MS-Off Ver
    PC 2016
    Posts
    87

    Re: Consolidation of multiple sheets to one sheet

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Consolidation of Data from Multiple Sheets
    By awmusgrove in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-12-2016, 12:03 PM
  2. consolidation from multiple sheets
    By leprince2007 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-08-2016, 11:40 AM
  3. Consolidation of a range in different sheets to a single sheet
    By laansesu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-26-2016, 06:08 AM
  4. Automatic consolidation of data from multiple sheets
    By ScottBeatty in forum Excel General
    Replies: 2
    Last Post: 07-23-2014, 11:31 AM
  5. Replies: 9
    Last Post: 05-03-2012, 10:29 AM
  6. Data consolidation from Multiple Sheets
    By wingale in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-14-2006, 12:30 PM
  7. [SOLVED] Multiple consolidation sheet
    By Navin in forum Excel General
    Replies: 0
    Last Post: 03-15-2006, 11:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1