Pivot Table from multiple sheets?
 

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

Pivot Table from multiple sheets?
 

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.)


New Delhi, India


"cware" wrote:

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

Pivot Table from multiple sheets?
 

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.


"cware" wrote:

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

Pivot Table from multiple sheets?
 

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
InsertModule
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

--
Regards
Roger Govier

"cware" wrote in message
...
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

Pivot Table from multiple sheets?
 

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.

"Roger Govier" wrote:

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
InsertModule
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

--
Regards
Roger Govier

"cware" wrote in message
...
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

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. WORKS GREAT!! Thanks again

"Roger Govier" wrote:

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
InsertModule
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

--
Regards
Roger Govier

"cware" wrote in message
...
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

Pivot Table from multiple sheets?
 

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 tabRename2008

--
Regards
Roger Govier

"cware" wrote in message
...
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.

"Roger Govier" wrote:

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
InsertModule
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

--
Regards
Roger Govier

"cware" wrote in message
...
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

Pivot Table from multiple sheets?
 

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 Govier" wrote:

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
InsertModule
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

--
Regards
Roger Govier

"cware" wrote in message
...
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

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



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:

On Monday, March 16, 2009 10:47 AM
cwar wrote:

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

On Monday, March 16, 2009 11:08 AM
DILipande wrote:

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.)


New

On Monday, March 16, 2009 11:15 AM
NDPar wrote:

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

On Monday, March 16, 2009 1:23 PM
Roger Govier wrote:

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

On Monday, March 16, 2009 3:32 PM
cwar wrote:

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.

On Monday, March 16, 2009 4:04 PM
cwar wrote:

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

On Monday, March 16, 2009 5:04 PM
Roger Govier wrote:

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

On Friday, June 19, 2009 4:21 PM
Laur wrote:

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/tutorials...ascript-w.aspx

Pivot table from multiple sheets
 

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:

On Monday, March 16, 2009 10:47 AM
cwar wrote:

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

On Monday, March 16, 2009 11:08 AM
DILipande wrote:

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.)


New

On Monday, March 16, 2009 11:15 AM
NDPar wrote:

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

On Monday, March 16, 2009 1:23 PM
Roger Govier wrote:

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

On Monday, March 16, 2009 3:32 PM
cwar wrote:

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.

On Monday, March 16, 2009 4:04 PM
cwar wrote:

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

On Monday, March 16, 2009 5:04 PM
Roger Govier wrote:

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

On Friday, June 19, 2009 4:21 PM
Laur wrote:

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

On Wednesday, October 21, 2009 2:54 PM
Mina Manick wrote:

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/tutorials...or-tablet.aspx

Pivot table from multiple sheets
 

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

"Mina Manick" wrote in message ...
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:

On Monday, March 16, 2009 10:47 AM
cwar wrote:

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

On Monday, March 16, 2009 11:08 AM
DILipande wrote:

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.)


New

On Monday, March 16, 2009 11:15 AM
NDPar wrote:

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

On Monday, March 16, 2009 1:23 PM
Roger Govier wrote:

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

On Monday, March 16, 2009 3:32 PM
cwar wrote:

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.

On Monday, March 16, 2009 4:04 PM
cwar wrote:

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

On Monday, March 16, 2009 5:04 PM
Roger Govier wrote:

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

On Friday, June 19, 2009 4:21 PM
Laur wrote:

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

On Wednesday, October 21, 2009 2:54 PM
Mina Manick wrote:

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/tutorials...or-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




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

The message was checked by ESET Smart Security.

http://www.eset.com

Pivot table from two excel sheets
 

see newer posting

--
Regards
Roger Govier

"Mina Manick" wrote 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:

On Monday, March 16, 2009 10:47 AM
cwar wrote:

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

On Monday, March 16, 2009 11:08 AM
DILipande wrote:

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.)


New

On Monday, March 16, 2009 11:15 AM
NDPar wrote:

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

On Monday, March 16, 2009 1:23 PM
Roger Govier wrote:

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

On Monday, March 16, 2009 3:32 PM
cwar wrote:

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.

On Monday, March 16, 2009 4:04 PM
cwar wrote:

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

On Monday, March 16, 2009 5:04 PM
Roger Govier wrote:

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

On Friday, June 19, 2009 4:21 PM
Laur wrote:

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/tutorials...ascript-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




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

The message was checked by ESET Smart Security.

http://www.eset.com

Pivot table from multiple sheets
 

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.

"Roger Govier" wrote:

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

"Mina Manick" wrote in message ...
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:

On Monday, March 16, 2009 10:47 AM
cwar wrote:

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

On Monday, March 16, 2009 11:08 AM
DILipande wrote:

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.)


New

On Monday, March 16, 2009 11:15 AM
NDPar wrote:

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

On Monday, March 16, 2009 1:23 PM
Roger Govier wrote:

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

On Monday, March 16, 2009 3:32 PM
cwar wrote:

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.

On Monday, March 16, 2009 4:04 PM
cwar wrote:

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

On Monday, March 16, 2009 5:04 PM
Roger Govier wrote:

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

On Friday, June 19, 2009 4:21 PM
Laur wrote:

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

On Wednesday, October 21, 2009 2:54 PM
Mina Manick wrote:

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/tutorials...or-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




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

The message was checked by ESET Smart Security.

http://www.eset.com



.