Pivot Table from multiple sheets?

  • Thread starter Thread starter cware
  • Start date Start date
C

cware

Does anybody know if I can create a Pivot Table using more than one sheet in
a workbook? I've been using Pivot Tables for a while and have never been
able to figure this out, but it would be a time saver for sure in my
processes.

Thanks

Cathy
 
Hi Cware,

You can choose the option "Multiple Consolidation Range" when you create the
pivot table.

Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India
 
It would be nice to know what version of Excel you are using; however, even
if you are using Excel 2007, you can use multiple ranges to summarize via a
pivot table, as long as they have the same fields, field names, etc.

While holding down the ALT key, first press the letter D, and then press the
letter P.

Then check the "Multiple consolidation ranges" and click Next.

When asked "How many page fields do you want?", for now, just click NEXT.

Then you can select different ranges even if they are in different
worksheets and/or workbooks.

Good Luck.
 
Hi Cathy

Using Multiple Consolidation does not work in quite the same way as normal,
and may not produce the results you want.
See here for more details
http://www.contextures.com/xlPivot08.html

If your combined number of rows of data is less than 65536 rows (XL2003 and
lower) or 1m rows XL2007, you can use the following code to consolidate all
of the data to a single sheet, then base your PT on the new sheet.
The code creates a new named range called myData, which can be used as the
Source when creating the new PT.

First insert a new sheet in your file and call it "All Data" and copy the
column headings from one of your existing sheets.
Note the settings for Lastcol and SourceCol in the code below, and change
those to match your sheet layout.
A new column is created on the combined sheet, which shows which sheet the
data came from, which can then be used as one of the selections in your PT.

Sub CombineSheets()
Dim wb As Workbook
Dim Sht As Worksheet, SummarySht As Worksheet
Dim NewRow As Long, LastRow As Long

Const Lastcol = "G" 'Set for last column of data
Const SourceCol = "H" ' next column to above
Application.ScreenUpdating = False
NewRow = 2
Set wb = ThisWorkbook
Set SummarySht = Sheets("All Data")
SummarySht.Range("2:65536").Delete

For Each Sht In ThisWorkbook.Sheets
'Check it is not a Report or Data Sheet
If InStr(Sht.Name, "Report") = 0 _
And InStr(Sht.Name, "Data") = 0 Then

LastRow = Sht.Range("A" & Rows.Count).End(xlUp).Row
If NewRow + LastRow > 65536 Then
MsgBox "Cannot consolidate all data " _
& "as there are too many rows"
GoTo Endsub
End If
Sht.Range("A2:" & Lastcol & LastRow).Copy _
SummarySht.Range("A" & NewRow)
SummarySht.Range(SourceCol & NewRow & ":" _
& SourceCol & LastRow + NewRow - 1) = Sht.Name
NewRow = NewRow + LastRow - 1
End If

Next Sht
Endsub:
With SummarySht
Columns("A:" & SourceCol).EntireColumn.AutoFit
Range(SourceCol & "1") = "Source"
LastRow = Range("A" & Rows.Count).End(xlUp).Row

wb.Names.Add Name:="myData", RefersTo:= _
"=" & Range(Cells(1, 1), Cells(LastRow, SourceCol)).Address

Application.ScreenUpdating = True
End With
End Sub


Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Alt+F8 to bring up Macros
Highlight the macro name
Run

Create a new PT using myData when it comes to asking for Source
 
Roger, thanks for the option. I tried Multiple Consolidation twice and it
bombed my Excel 2003 application, so it was nice to have an alternative. I'm
just now learning about VBE and it's value.

Can you also assist on writing something to run in front of this specific
combination that would add a column and header at the end of my data, with a
specific value? One sheet is for 2008 and one for 2009, and I now am going
in and manually typing in 2008 and copying down to end of data and then the
same for 2009. Since the data is always going to have the same number of
column with always the same headers, but the data itself changes monthly.
Just trying to automate more of the manual stuff.....Thanks if you can.
 
Hi Roger:

If you get this, please disregard my last request for help to you
specifically. I figured out how to name the tabs by the year, and the source
column will fill in from there what I need. WORKS GREAT!! Thanks again
 
Hi Cathy

The code takes the value of the Source Sheet Name, and inserts it in a
column just to the right of your existing data.
As I said in my earlier posting, this can then be used as one of the fields
in the PT.
If instead of Sheet names Sheet1 Sheet2 etc., you change them to 2008, 2009
and so on, then you have what you need.
Right click on sheet tab>Rename>2008
 
Hi Roger.

I am so glad that you posted this. You saved my life today!! I have
another problem though that maybe you can help me with. If I continually run
the macro, It includes the summary sheet and the pivot table. How can I
adjust the macro to not include those 2 worksheets?

Thanks.
Laura
 
Roger,

I used your code in my workbook with three or four worksheets but I want to pull data only from two sheets. Please help.

Thanks,
Mina



Laur wrote:

Hi Roger.I am so glad that you posted this. You saved my life today!!
19-Jun-09

Hi Roger

I am so glad that you posted this. You saved my life today!! I hav
another problem though that maybe you can help me with. If I continually ru
the macro, It includes the summary sheet

Previous Posts In This Thread:

Pivot Table from multiple sheets?
Does anybody know if I can create a Pivot Table using more than one sheet i
a workbook? I have been using Pivot Tables for a while and have never bee
able to figure this out, but it would be a time

Hi Cware,You can choose the option "Multiple Consolidation Range" when you
Hi Cware

You can choose the option "Multiple Consolidation Range" when you create th
pivot table

Thanks
-
Dilip Kumar Pande
MBA, BCA, B.Com(Hons.
(e-mail address removed)
(e-mail address removed)
New

It would be nice to know what version of Excel you are using; however, even if
It would be nice to know what version of Excel you are using; however, eve
if you are using Excel 2007, you can use multiple ranges to summarize via
pivot table, as long as they have the same field

Hi CathyUsing Multiple Consolidation does not work in quite the same way as
Hi Cath

Using Multiple Consolidation does not work in quite the same way as normal
and may not produce the results you want
See here for more detail
http://www.contextures.com/xlPivot08.htm

If

Roger, thanks for the option.
Roger, thanks for the option. I tried Multiple Consolidation twice and i
bombed my Excel 2003 application, so it was nice to have an alternative. I a
just now learning about VBE and it is value.

Re: Pivot Table from multiple sheets?
Hi Roger

If you get this, please disregard my last request for help to yo
specifically. I figured out how to name the tabs by the year, and the sourc
column will fill in from there what I need. W

Hi CathyThe code takes the value of the Source Sheet Name, and inserts it in a
Hi Cath

The code takes the value of the Source Sheet Name, and inserts it in
column just to the right of your existing data
As I said in my earlier posting, this can then be used as one of the fi

Hi Roger.I am so glad that you posted this. You saved my life today!!
Hi Roger

I am so glad that you posted this. You saved my life today!! I hav
another problem though that maybe you can help me with. If I continually ru
the macro, It includes the summary sheet

EggHeadCafe - Software Developer Portal of Choice
Auto Save In JavaScript With window.setTimeout
http://www.eggheadcafe.com/tutorial...8-aac1f8c62141/auto-save-in-javascript-w.aspx
 
Laura,

Did you find the resolution for the problem you stated here? If so, please post the solution. It would be a life saver for me.

Thanks,
Mina



Laur wrote:

Hi Roger.I am so glad that you posted this. You saved my life today!!
19-Jun-09

Hi Roger

I am so glad that you posted this. You saved my life today!! I hav
another problem though that maybe you can help me with. If I continually ru
the macro, It includes the summary sheet

Previous Posts In This Thread:

Pivot Table from multiple sheets?
Does anybody know if I can create a Pivot Table using more than one sheet i
a workbook? I have been using Pivot Tables for a while and have never bee
able to figure this out, but it would be a time

Hi Cware,You can choose the option "Multiple Consolidation Range" when you
Hi Cware

You can choose the option "Multiple Consolidation Range" when you create th
pivot table

Thanks
-
Dilip Kumar Pande
MBA, BCA, B.Com(Hons.
(e-mail address removed)
(e-mail address removed)
New

It would be nice to know what version of Excel you are using; however, even if
It would be nice to know what version of Excel you are using; however, eve
if you are using Excel 2007, you can use multiple ranges to summarize via
pivot table, as long as they have the same field

Hi CathyUsing Multiple Consolidation does not work in quite the same way as
Hi Cath

Using Multiple Consolidation does not work in quite the same way as normal
and may not produce the results you want
See here for more detail
http://www.contextures.com/xlPivot08.htm

If

Roger, thanks for the option.
Roger, thanks for the option. I tried Multiple Consolidation twice and i
bombed my Excel 2003 application, so it was nice to have an alternative. I a
just now learning about VBE and it is value.

Re: Pivot Table from multiple sheets?
Hi Roger

If you get this, please disregard my last request for help to yo
specifically. I figured out how to name the tabs by the year, and the sourc
column will fill in from there what I need. W

Hi CathyThe code takes the value of the Source Sheet Name, and inserts it in a
Hi Cath

The code takes the value of the Source Sheet Name, and inserts it in
column just to the right of your existing data
As I said in my earlier posting, this can then be used as one of the fi

Hi Roger.I am so glad that you posted this. You saved my life today!!
Hi Roger

I am so glad that you posted this. You saved my life today!! I hav
another problem though that maybe you can help me with. If I continually ru
the macro, It includes the summary sheet

Pivot table from two excel sheets
Roger,

I used your code in my workbook with three or four worksheets but I want to pull data only from two sheets. Please help.

Thanks,
Mina

EggHeadCafe - Software Developer Portal of Choice
Compressed Ink for Tablet PC and Windows XP
http://www.eggheadcafe.com/tutorial...5-48e1700cf5a9/compressed-ink-for-tablet.aspx
 
Hi Mina

I can't find my original post in answer to Laura.
If you want to mail me direct with a copy of your workbook, and what you are
trying to achieve, I will try and sort it out for you.
To mail direct
roger at technology4u dot co dot uk
Do the obvious with at and dot to make valid email address.

--
Regards
Roger Govier

Laura,

Did you find the resolution for the problem you stated here? If so, please
post the solution. It would be a life saver for me.

Thanks,
Mina



Laur wrote:

Hi Roger.I am so glad that you posted this. You saved my life today!!
19-Jun-09

Hi Roger.

I am so glad that you posted this. You saved my life today!! I have
another problem though that maybe you can help me with. If I continually
run
the macro, It includes the summary sheet

Previous Posts In This Thread:

Pivot Table from multiple sheets?
Does anybody know if I can create a Pivot Table using more than one sheet
in
a workbook? I have been using Pivot Tables for a while and have never
been
able to figure this out, but it would be a time

Hi Cware,You can choose the option "Multiple Consolidation Range" when you
Hi Cware,

You can choose the option "Multiple Consolidation Range" when you create
the
pivot table.

Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New

It would be nice to know what version of Excel you are using; however,
even if
It would be nice to know what version of Excel you are using; however,
even
if you are using Excel 2007, you can use multiple ranges to summarize via
a
pivot table, as long as they have the same field

Hi CathyUsing Multiple Consolidation does not work in quite the same way
as
Hi Cathy

Using Multiple Consolidation does not work in quite the same way as
normal,
and may not produce the results you want.
See here for more details
http://www.contextures.com/xlPivot08.html

If

Roger, thanks for the option.
Roger, thanks for the option. I tried Multiple Consolidation twice and it
bombed my Excel 2003 application, so it was nice to have an alternative.
I am
just now learning about VBE and it is value.

Re: Pivot Table from multiple sheets?
Hi Roger:

If you get this, please disregard my last request for help to you
specifically. I figured out how to name the tabs by the year, and the
source
column will fill in from there what I need. W

Hi CathyThe code takes the value of the Source Sheet Name, and inserts it
in a
Hi Cathy

The code takes the value of the Source Sheet Name, and inserts it in a
column just to the right of your existing data.
As I said in my earlier posting, this can then be used as one of the fi

Hi Roger.I am so glad that you posted this. You saved my life today!!
Hi Roger.

I am so glad that you posted this. You saved my life today!! I have
another problem though that maybe you can help me with. If I continually
run
the macro, It includes the summary sheet

Pivot table from two excel sheets
Roger,

I used your code in my workbook with three or four worksheets but I want
to pull data only from two sheets. Please help.

Thanks,
Mina

EggHeadCafe - Software Developer Portal of Choice
Compressed Ink for Tablet PC and Windows XP
http://www.eggheadcafe.com/tutorial...5-48e1700cf5a9/compressed-ink-for-tablet.aspx

__________ Information from ESET Smart Security, version of virus
signature database 4530 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4530 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
see newer posting

--
Regards
Roger Govier

in message
Roger,

I used your code in my workbook with three or four worksheets but I want
to pull data only from two sheets. Please help.

Thanks,
Mina



Laur wrote:

Hi Roger.I am so glad that you posted this. You saved my life today!!
19-Jun-09

Hi Roger.

I am so glad that you posted this. You saved my life today!! I have
another problem though that maybe you can help me with. If I continually
run
the macro, It includes the summary sheet

Previous Posts In This Thread:

Pivot Table from multiple sheets?
Does anybody know if I can create a Pivot Table using more than one sheet
in
a workbook? I have been using Pivot Tables for a while and have never
been
able to figure this out, but it would be a time

Hi Cware,You can choose the option "Multiple Consolidation Range" when you
Hi Cware,

You can choose the option "Multiple Consolidation Range" when you create
the
pivot table.

Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New

It would be nice to know what version of Excel you are using; however,
even if
It would be nice to know what version of Excel you are using; however,
even
if you are using Excel 2007, you can use multiple ranges to summarize via
a
pivot table, as long as they have the same field

Hi CathyUsing Multiple Consolidation does not work in quite the same way
as
Hi Cathy

Using Multiple Consolidation does not work in quite the same way as
normal,
and may not produce the results you want.
See here for more details
http://www.contextures.com/xlPivot08.html

If

Roger, thanks for the option.
Roger, thanks for the option. I tried Multiple Consolidation twice and it
bombed my Excel 2003 application, so it was nice to have an alternative.
I am
just now learning about VBE and it is value.

Re: Pivot Table from multiple sheets?
Hi Roger:

If you get this, please disregard my last request for help to you
specifically. I figured out how to name the tabs by the year, and the
source
column will fill in from there what I need. W

Hi CathyThe code takes the value of the Source Sheet Name, and inserts it
in a
Hi Cathy

The code takes the value of the Source Sheet Name, and inserts it in a
column just to the right of your existing data.
As I said in my earlier posting, this can then be used as one of the fi

Hi Roger.I am so glad that you posted this. You saved my life today!!
Hi Roger.

I am so glad that you posted this. You saved my life today!! I have
another problem though that maybe you can help me with. If I continually
run
the macro, It includes the summary sheet

EggHeadCafe - Software Developer Portal of Choice
Auto Save In JavaScript With window.setTimeout
http://www.eggheadcafe.com/tutorial...8-aac1f8c62141/auto-save-in-javascript-w.aspx

__________ Information from ESET Smart Security, version of virus
signature database 4530 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4530 (20091021) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
I just wanted to say thank you Roger, pulling the information into a
consolidated sheet and then creating the Pivot table, your post saved me a
few hours of VBE time. Thank you so much.
 
Back
Top