+ Reply to Thread
Results 1 to 7 of 7

Consolidated Summary Sheet - Copy data from one sheet to other based on criteria

  1. #1
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Consolidated Summary Sheet - Copy data from one sheet to other based on criteria

    Hello.

    I need help from VBA programmers out here with a macro that can copy data from various sheets into a summary sheet based on criteria. Applying a logic seems to be difficult because there are merged cells in raw data, the count of sheets can increase, count of items can increase and client name needs to be picked up from sheet name. Its getting too difficult for me. Appreciate if someone can help. I have attached the file with this question.

    1. Every client would have two sheets. One for Stock and One for Sale. In this file, there are only two clients (20 and 50) but actual file may have more than two.
    2. The quantity from both these sheets for that particular client needs to be populated in Summary sheet.
    3. Data that needs to be picked up from various sheets and that needs to populated in summary sheet is marked in red color.
    4. The summary sheet has monthly periods (From and To). In this file, there are five periods defined (Apr/May/Jun/Jul/Aug) and only three periods populated because in Raw sheet there is no data for Jul and Aug. The summary sheet may have more than five periods too.
    5. There are 12 items given in the file. It can be more than 12 in actual file.

    The macro should fill the quantities for every client mentioned in the summary sheet and for all the periods (for demo purpose, I have shown BEFORE and AFTER data in summary sheet) I want the BEFORE table to get automatically populated at a click of a button so that it looks like AFTER. In the example, there is no data for 1-Jul-2015 to 31-Jul-2015 and 1-Aug-2015 to 30-Aug-2015 because it is not available in the raw sheets. It may be available even for more periods. On the other hand, if the data is available for all periods in raw sheet and if I need to process data only for two periods, I will keep only these two dates active in row2 of summary sheet and delete other dates so that the macro should understand that I need data only for two periods.
    Attached Files Attached Files
    Last edited by sabha; 10-21-2015 at 12:48 AM.

  2. #2
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Re: Consolidated Summary Sheet - Copy data from one sheet to other based on criteria

    Awaiting a reply. Please help.

  3. #3
    Valued Forum Contributor mangesh.mehendale's Avatar
    Join Date
    06-04-2015
    Location
    India
    MS-Off Ver
    2007
    Posts
    510

    Re: Consolidated Summary Sheet - Copy data from one sheet to other based on criteria

    hiii is it okay if done with formula??

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Don`t care, take care...

    Regards,
    Mangesh

  4. #4
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Re: Consolidated Summary Sheet - Copy data from one sheet to other based on criteria

    Looks good. However, I am wondering how would you track the periods given in row2 and row17. Can something be factored into the formula or do I need to keep changing formulas for all date ranges. Thanks for your help

  5. #5
    Valued Forum Contributor mangesh.mehendale's Avatar
    Join Date
    06-04-2015
    Location
    India
    MS-Off Ver
    2007
    Posts
    510

    Re: Consolidated Summary Sheet - Copy data from one sheet to other based on criteria

    okay then try this..

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    see attached file..
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Re: Consolidated Summary Sheet - Copy data from one sheet to other based on criteria

    Thanks
    God bless

  7. #7
    Valued Forum Contributor mangesh.mehendale's Avatar
    Join Date
    06-04-2015
    Location
    India
    MS-Off Ver
    2007
    Posts
    510

    Re: Consolidated Summary Sheet - Copy data from one sheet to other based on criteria

    Okay ... mark this thread as solved..

    "Add reputation" to answer if done..

+ 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. Generating a data summary sheet based on multiple criteria
    By Anuru in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 07-18-2014, 08:03 PM
  2. [SOLVED] Macro to consolidated all data from different sheets to summary sheet in the same workbook
    By rochakmehta90 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-17-2013, 03:10 AM
  3. Copy data from all but a few worksheets into a summary sheet based on condition
    By d_kjellin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-04-2013, 12:27 PM
  4. Replies: 1
    Last Post: 03-28-2013, 02:49 PM
  5. [SOLVED] Copy and paste data from sheet 2 to sheet 1 based on specific criteria on sheet 1
    By VBADUD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2012, 04:18 AM
  6. consolidated data in summary sheet
    By ivankaka in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-26-2012, 11:58 AM
  7. Replies: 4
    Last Post: 07-24-2012, 07:09 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