Pivot table from multiple ranges? (Revisited)

[Steve Vincent]

Hello,

I know this question has been posted, but I will ask again: Can I create a
Pivot Table from two separate data ranges, e.g., different months' data on
different sheets? The data are laid out identically, i.e., same column
headings, same number of columns. Just different rows/records.

I have already scoured the postings on this topic and have tried the
solutions offered, and (1) consolidation doesn't give me useful results, and
(2) creating a MS Query doesn't work because the two ranges in question are
not joined or linked by a common field, but are actually two sets of
identically-laid-out data.

Is there actually no way to select-to-combine two non-adjacent ranges/lists
in a workbook to combine into one Pivot Table?

Thanks in advance for any advice you can offer,

Steve
(e-mail address removed)

 

[Debra Dalgleish]

You can use a Union query to combine the data from the ranges. There's a
sample here:

http://www.contextures.com/excelfiles.html

Under PivotTables, look for PT0007 - Multiple Consolidation Ranges

 

[Steve Vincent]

Debra, thanks so much for pointing me to the right place at your site. The
Union query example was right on target for me. Your site is an excellent
resource.

Thanks again,
Steve

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that the example helped.

 

[EEA]

Does this technique work if the multiple worksheets (datasources) are in the
same workbook as the pivot table?

I have vba code that updates the data on each of the worksheets..after the
worksheets are updated i wanted to refresh the pivot table in the vba code. I
am getting an error message that the connection to the datasource has been
lost when trying to execute the refresh command. If i close the VBA window, I
can successfully refresh the pivot table.