+ Reply to Thread
Results 1 to 7 of 7

How to consolidate multiple workbooks into summary workbook using VBA

  1. #1
    Forum Contributor
    Join Date
    08-19-2016
    Location
    Chennai, India
    MS-Off Ver
    2007
    Posts
    300

    How to consolidate multiple workbooks into summary workbook using VBA

    Dear Forum Members,

    Due to very urgent situation, i am reposting this thread and not aware about policy to repost and apologies for that.
    1. I need your guidance and technical inputs in the following requirements:
    2. I have a master data set (workbook) to collect the details of visitors to the counselling center regularly, which has been shared with various 30 counselling centers in the state.
    3. I want to consolidate these 30 counselling centers master data set (Specific Sheet from the workbooks and sheet name is "Master Sheet") on every month.
    4. The consolidation should be done in the "Summary Sheet" (same master data set template and already has a header).
    5. Master data set range is A5 : CQ5004 (Data starts from A5) and I want to consolidate only data filed rows and should ignored blank rows in each workbook.
    6. I have used the following VBA code and it works well, but it consolidates all the blank rows also. But i need only data filed row
    s.
    Please Login or Register  to view this content.

    Appreciate your help and support on this.

    Regards

    Bala

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: How to consolidate multiple workbooks into summary workbook using VBA

    You could just add the following at the end of your Sub

    Please Login or Register  to view this content.
    Which will filter on blanks in column A and delete the rows.

    (please make a backup of your sheet before testing it though!)
    If someone has helped you then please add to their Reputation

  3. #3
    Forum Contributor
    Join Date
    08-19-2016
    Location
    Chennai, India
    MS-Off Ver
    2007
    Posts
    300

    Re: How to consolidate multiple workbooks into summary workbook using VBA

    Dear Mr. PJ,

    Thanks for your timely support and help. When i was testing, its work well. As your narrated, it is filtered and delete the non data rows. But finally the merged entire set of data was also deleted.

    Hope the problem is in this line of code. Kindly help me on this.

    Please Login or Register  to view this content.
    Regards

  4. #4
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: How to consolidate multiple workbooks into summary workbook using VBA

    Im not sure on that because that code worked for me. I had 20 rows, 5 of which were blank and it removed the blank ones only.

    Can you post your workbook?

    Actually, can you post your Sub so I can see where and how the code has been added?

  5. #5
    Forum Contributor
    Join Date
    08-19-2016
    Location
    Chennai, India
    MS-Off Ver
    2007
    Posts
    300

    Re: How to consolidate multiple workbooks into summary workbook using VBA

    Here is the code

    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: How to consolidate multiple workbooks into summary workbook using VBA

    Although I dont see how the error could cause the data to all be deleted you have put the code in the wrong place. At the moment youre running it for every workbook whereas you simply need to do it once at the very end.

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    08-19-2016
    Location
    Chennai, India
    MS-Off Ver
    2007
    Posts
    300

    Re: How to consolidate multiple workbooks into summary workbook using VBA

    Dear Mr. PJ,

    It works well and thanks a lot. But every time i use the macro, very 1st line is also deleting, due to autofilter = 1 (Code), other than that, its awesome

+ 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. Replies: 1
    Last Post: 12-15-2016, 04:26 AM
  2. Consolidate Multiple Workbooks With Same Column Header into 1 Master Workbook
    By markusvirus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-21-2016, 02:55 PM
  3. VBA Code to consolidate sheets from multiple workbooks into one workbook
    By d247 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-10-2014, 02:53 AM
  4. Need to Consolidate multiple workbooks in to a Master Workbook w/ files on SharePoint
    By Vu Compra in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-31-2013, 10:34 AM
  5. [SOLVED] Consolidate data from workbooks and create link formulas in the summary workbook
    By sans in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-02-2013, 04:42 PM
  6. [SOLVED] Consolidate data from multiple workbooks into 1 workbook
    By VBA FTW in forum Excel Programming / VBA / Macros
    Replies: 30
    Last Post: 12-17-2012, 01:11 PM
  7. Replies: 20
    Last Post: 10-19-2012, 04:35 PM

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