Excel Pivot Table from Multiple Sheets Update

If you have similar data on two or more worksheets, you might want to combine that data in a pivot table, to show the summarized results. Unfortunately, the pivot table from data on multiple sheets can be a disappointment.

pivotmultipleconsol01

Create a Pivot Table with Programming

A couple of years ago, Excel MVP, Kirill Lapin (KL), shared a sample file that he created, with amendments by Hector Miguel Orozco Diaz. It uses code to automatically create a pivot table from multiple sheets in a workbook.

You can read the details here: Create a Pivot Table from Multiple Sheets.

Revised Solution

Kirill's sample file was created as a conceptual prototype, and targeted advanced VBA users. The code has minimal error handling and compatibility checks.

However, the sample file was extremely popular, and Excel users at all skill levels wanted to adopt this solution in their own applications. To make things easier, Kirill has created a similar solution based on ADO.

Advantages:

  1. No need for temporary file generation
  2. The code is faster and less prone to errors

Disadvantages:

  1. No manual refresh of the PivotTable
  2. Need to rebuild connection from the scratch to update the cache with new data

Download the ADO Sample File

You can download the new ADO version of the file from the Contextures website: PT0024 - Pivot Table from Multiple Sheets - ADO version. There is also a "Plug and Play" version of the file, at the same link.

________________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

2 comments to Excel Pivot Table from Multiple Sheets Update

  • Philippe BÉRARD

    Hello,
    Is it possible to consolidate not only sheets (which assumes that titles of the fileds are in the row) but also ranges.
    That could allow a more flexible position of the databases on each sheet.

    Thanks in advance for your return

    Best regards

    Philippe

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>