I have two worksheets containing 6 columns of data. The data in the first
worksheets represents customer orders at a very detailed level. The second
worksheet shows shipments on those orders at a much less detailed level. The
columns in both worksheets contain data that when I paste one on top of the
other in a third excel worksheet provides meaningful analysis for management.
We'd like to elimate the cutting and pasting in order to make the tool more
userfriendly.

I tried to use pivot tables from multiple ranges and found that while they
work, the pivot table is much less meaninful when using that fuctionality.

Using a macro or visual basic, is there a way to combine the data in the two
worksheets in a third worksheet that can be summarized in a pivot table?

hmm..
what a dangerous nickname to use in this group.
<VBG

have a look at my MultiRange Pivot addin.
http://members.chello.nl/keepitcool/download.html
code is open and may serve as an example.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Excel GuRu wrote in


I have two worksheets containing 6 columns of data. The data in the
first worksheets represents customer orders at a very detailed level.
The second worksheet shows shipments on those orders at a much less
detailed level. The columns in both worksheets contain data that
when I paste one on top of the other in a third excel worksheet
provides meaningful analysis for management. We'd like to elimate
the cutting and pasting in order to make the tool more userfriendly.

I tried to use pivot tables from multiple ranges and found that while
they work, the pivot table is much less meaninful when using that
fuctionality.

Using a macro or visual basic, is there a way to combine the data in
the two worksheets in a third worksheet that can be summarized in a
pivot table?

I noted the solution posted on the Contextures website of how to overcome the
limitations of multiple consolidation ranges in Pivot tables. That seemed to
work well, I will compare it to the addin you have pointed me towards.

http://www.contextures.com/xlPivot08.html#Limitations
"keepITcool" wrote:

hmm..
what a dangerous nickname to use in this group.
<VBG

have a look at my MultiRange Pivot addin.
http://members.chello.nl/keepitcool/download.html
code is open and may serve as an example.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Excel GuRu wrote in


I have two worksheets containing 6 columns of data. The data in the
first worksheets represents customer orders at a very detailed level.
The second worksheet shows shipments on those orders at a much less
detailed level. The columns in both worksheets contain data that
when I paste one on top of the other in a third excel worksheet
provides meaningful analysis for management. We'd like to elimate
the cutting and pasting in order to make the tool more userfriendly.

I tried to use pivot tables from multiple ranges and found that while
they work, the pivot table is much less meaninful when using that
fuctionality.

Using a macro or visual basic, is there a way to combine the data in
the two worksheets in a third worksheet that can be summarized in a
pivot table?