| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I Create another pivot table, based on the first one. Thank
I have a Pivot Table with the information as follow
Account Manager Region Total Sales Total Margin What I need is a graphic that contains only this fields Account Manager and Total Margin I was trying to copy the pivot table and select only the fields needed and create the graph from here but I didn't succeed. I need as well that every time I refresh the first pivot table the 2nd one is updated. Thank you in advance for any help |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I Create another pivot table, based on the first one. Thank
Hi Eduardo
Create a second Pivot Table based upon the same data as the first. Say yes to using same source to reduce memory requirements. In the second PT just add Manager to Row area and Margin to Data area. Click on any cell in this second PTPress F11 That will create a Chart for you. Right click on the Chart to select the Type you want -- Regards Roger Govier "Eduardo" wrote in message ... I have a Pivot Table with the information as follow Account Manager Region Total Sales Total Margin What I need is a graphic that contains only this fields Account Manager and Total Margin I was trying to copy the pivot table and select only the fields needed and create the graph from here but I didn't succeed. I need as well that every time I refresh the first pivot table the 2nd one is updated. Thank you in advance for any help |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I Create another pivot table, based on the first one. T
Hi Roger,
Thank you for helping me. I was able to create the PV however I have a button that I hit and prompt me to enter the dates I want the information to be reported let's say from 01/07/08 to 31/07/08, and when running this macro it doesn't actualize the new PV, can you help me?, thank you "Roger Govier" wrote: Hi Eduardo Create a second Pivot Table based upon the same data as the first. Say yes to using same source to reduce memory requirements. In the second PT just add Manager to Row area and Margin to Data area. Click on any cell in this second PTPress F11 That will create a Chart for you. Right click on the Chart to select the Type you want -- Regards Roger Govier "Eduardo" wrote in message ... I have a Pivot Table with the information as follow Account Manager Region Total Sales Total Margin What I need is a graphic that contains only this fields Account Manager and Total Margin I was trying to copy the pivot table and select only the fields needed and create the graph from here but I didn't succeed. I need as well that every time I refresh the first pivot table the 2nd one is updated. Thank you in advance for any help |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I Create another pivot table, based on the first one. T
Hi Eduardo
Post the code you are using -- Regards Roger Govier "Eduardo" wrote in message ... Hi Roger, Thank you for helping me. I was able to create the PV however I have a button that I hit and prompt me to enter the dates I want the information to be reported let's say from 01/07/08 to 31/07/08, and when running this macro it doesn't actualize the new PV, can you help me?, thank you "Roger Govier" wrote: Hi Eduardo Create a second Pivot Table based upon the same data as the first. Say yes to using same source to reduce memory requirements. In the second PT just add Manager to Row area and Margin to Data area. Click on any cell in this second PTPress F11 That will create a Chart for you. Right click on the Chart to select the Type you want -- Regards Roger Govier "Eduardo" wrote in message ... I have a Pivot Table with the information as follow Account Manager Region Total Sales Total Margin What I need is a graphic that contains only this fields Account Manager and Total Margin I was trying to copy the pivot table and select only the fields needed and create the graph from here but I didn't succeed. I need as well that every time I refresh the first pivot table the 2nd one is updated. Thank you in advance for any help |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I Create another pivot table, based on the first one. T
Hi Roger, below is the code, this is an spreadsheet I have received. The
company has a system to enter the SO and prepare the invoices, so with the below instruction I enter the period and it bring the total invoiced Sub RefreshPivot() Dim asDataSource(2) As String Application.ScreenUpdating = False ' Create Connection String Dim intResult As Integer intResult = GetUserLogin If intResult = 0 Then ' Get Date Entries gsStartDate = frmRefreshDates.txtStartDate.Text gsEndDate = frmRefreshDates.txtEndDate.Text ' Close the dates form frmRefreshDates.Hide ' Hard code the site for now. giSiteID = 1 ' Get to a cell located within the pivot table. ActiveSheet.PivotTables("ONYX1").TableRange1.Selec t ' Get the SQL connection and query strings associated with the pivot table. ' asDataSource = ActiveCell.PivotTable.SourceData asDataSource(1) = strConstring asDataSource(2) = "exec " + strFilename + " " + Str(giSiteID) + "," + Chr$(34) + gsStartDate + Chr$(34) + "," + Chr$(34) + gsEndDate + Chr$(34) On Error GoTo ErrorHandler 'Reset the pivot table data source ActiveSheet.PivotTableWizard xlExternal, asDataSource, ActiveSheet.Range("B8") GoTo Bye Else Exit Sub End If ErrorHandler: MsgBox (Error()) 'MsgBox "Problem retrieving data. It is possible that no rows met your search criteria." Bye: Application.ScreenUpdating = True End Sub "Roger Govier" wrote: Hi Eduardo Post the code you are using -- Regards Roger Govier "Eduardo" wrote in message ... Hi Roger, Thank you for helping me. I was able to create the PV however I have a button that I hit and prompt me to enter the dates I want the information to be reported let's say from 01/07/08 to 31/07/08, and when running this macro it doesn't actualize the new PV, can you help me?, thank you "Roger Govier" wrote: Hi Eduardo Create a second Pivot Table based upon the same data as the first. Say yes to using same source to reduce memory requirements. In the second PT just add Manager to Row area and Margin to Data area. Click on any cell in this second PTPress F11 That will create a Chart for you. Right click on the Chart to select the Type you want -- Regards Roger Govier "Eduardo" wrote in message ... I have a Pivot Table with the information as follow Account Manager Region Total Sales Total Margin What I need is a graphic that contains only this fields Account Manager and Total Margin I was trying to copy the pivot table and select only the fields needed and create the graph from here but I didn't succeed. I need as well that every time I refresh the first pivot table the 2nd one is updated. Thank you in advance for any help |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I Create another pivot table, based on the first one. T
Hi Eduardo
Try entering the following line before your Goto Bye ActiveSheet.PivotTables("ONYX1").PivotCache.Refres h -- Regards Roger Govier "Eduardo" wrote in message ... Hi Roger, below is the code, this is an spreadsheet I have received. The company has a system to enter the SO and prepare the invoices, so with the below instruction I enter the period and it bring the total invoiced Sub RefreshPivot() Dim asDataSource(2) As String Application.ScreenUpdating = False ' Create Connection String Dim intResult As Integer intResult = GetUserLogin If intResult = 0 Then ' Get Date Entries gsStartDate = frmRefreshDates.txtStartDate.Text gsEndDate = frmRefreshDates.txtEndDate.Text ' Close the dates form frmRefreshDates.Hide ' Hard code the site for now. giSiteID = 1 ' Get to a cell located within the pivot table. ActiveSheet.PivotTables("ONYX1").TableRange1.Selec t ' Get the SQL connection and query strings associated with the pivot table. ' asDataSource = ActiveCell.PivotTable.SourceData asDataSource(1) = strConstring asDataSource(2) = "exec " + strFilename + " " + Str(giSiteID) + "," + Chr$(34) + gsStartDate + Chr$(34) + "," + Chr$(34) + gsEndDate + Chr$(34) On Error GoTo ErrorHandler 'Reset the pivot table data source ActiveSheet.PivotTableWizard xlExternal, asDataSource, ActiveSheet.Range("B8") GoTo Bye Else Exit Sub End If ErrorHandler: MsgBox (Error()) 'MsgBox "Problem retrieving data. It is possible that no rows met your search criteria." Bye: Application.ScreenUpdating = True End Sub "Roger Govier" wrote: Hi Eduardo Post the code you are using -- Regards Roger Govier "Eduardo" wrote in message ... Hi Roger, Thank you for helping me. I was able to create the PV however I have a button that I hit and prompt me to enter the dates I want the information to be reported let's say from 01/07/08 to 31/07/08, and when running this macro it doesn't actualize the new PV, can you help me?, thank you "Roger Govier" wrote: Hi Eduardo Create a second Pivot Table based upon the same data as the first. Say yes to using same source to reduce memory requirements. In the second PT just add Manager to Row area and Margin to Data area. Click on any cell in this second PTPress F11 That will create a Chart for you. Right click on the Chart to select the Type you want -- Regards Roger Govier "Eduardo" wrote in message ... I have a Pivot Table with the information as follow Account Manager Region Total Sales Total Margin What I need is a graphic that contains only this fields Account Manager and Total Margin I was trying to copy the pivot table and select only the fields needed and create the graph from here but I didn't succeed. I need as well that every time I refresh the first pivot table the 2nd one is updated. Thank you in advance for any help |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I Create another pivot table, based on the first one. T
Hi Roger, I added the code and it didn't work, however I realized that Onix1
is the name of my PV so I went to the other new PV and as the name was PivotTable2 I change to it in your code, now I get this message error "Unable to get the PivotTable property of the worksheet class" "Roger Govier" wrote: Hi Eduardo Try entering the following line before your Goto Bye ActiveSheet.PivotTables("ONYX1").PivotCache.Refres h -- Regards Roger Govier "Eduardo" wrote in message ... Hi Roger, below is the code, this is an spreadsheet I have received. The company has a system to enter the SO and prepare the invoices, so with the below instruction I enter the period and it bring the total invoiced Sub RefreshPivot() Dim asDataSource(2) As String Application.ScreenUpdating = False ' Create Connection String Dim intResult As Integer intResult = GetUserLogin If intResult = 0 Then ' Get Date Entries gsStartDate = frmRefreshDates.txtStartDate.Text gsEndDate = frmRefreshDates.txtEndDate.Text ' Close the dates form frmRefreshDates.Hide ' Hard code the site for now. giSiteID = 1 ' Get to a cell located within the pivot table. ActiveSheet.PivotTables("ONYX1").TableRange1.Selec t ' Get the SQL connection and query strings associated with the pivot table. ' asDataSource = ActiveCell.PivotTable.SourceData asDataSource(1) = strConstring asDataSource(2) = "exec " + strFilename + " " + Str(giSiteID) + "," + Chr$(34) + gsStartDate + Chr$(34) + "," + Chr$(34) + gsEndDate + Chr$(34) On Error GoTo ErrorHandler 'Reset the pivot table data source ActiveSheet.PivotTableWizard xlExternal, asDataSource, ActiveSheet.Range("B8") GoTo Bye Else Exit Sub End If ErrorHandler: MsgBox (Error()) 'MsgBox "Problem retrieving data. It is possible that no rows met your search criteria." Bye: Application.ScreenUpdating = True End Sub "Roger Govier" wrote: Hi Eduardo Post the code you are using -- Regards Roger Govier "Eduardo" wrote in message ... Hi Roger, Thank you for helping me. I was able to create the PV however I have a button that I hit and prompt me to enter the dates I want the information to be reported let's say from 01/07/08 to 31/07/08, and when running this macro it doesn't actualize the new PV, can you help me?, thank you "Roger Govier" wrote: Hi Eduardo Create a second Pivot Table based upon the same data as the first. Say yes to using same source to reduce memory requirements. In the second PT just add Manager to Row area and Margin to Data area. Click on any cell in this second PTPress F11 That will create a Chart for you. Right click on the Chart to select the Type you want -- Regards Roger Govier "Eduardo" wrote in message ... I have a Pivot Table with the information as follow Account Manager Region Total Sales Total Margin What I need is a graphic that contains only this fields Account Manager and Total Margin I was trying to copy the pivot table and select only the fields needed and create the graph from here but I didn't succeed. I need as well that every time I refresh the first pivot table the 2nd one is updated. Thank you in advance for any help |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I Create another pivot table, based on the first one. T
Did you put it in quotes?
ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh -- Regards Roger Govier "Eduardo" wrote in message ... Hi Roger, I added the code and it didn't work, however I realized that Onix1 is the name of my PV so I went to the other new PV and as the name was PivotTable2 I change to it in your code, now I get this message error "Unable to get the PivotTable property of the worksheet class" "Roger Govier" wrote: Hi Eduardo Try entering the following line before your Goto Bye ActiveSheet.PivotTables("ONYX1").PivotCache.Refres h -- Regards Roger Govier "Eduardo" wrote in message ... Hi Roger, below is the code, this is an spreadsheet I have received. The company has a system to enter the SO and prepare the invoices, so with the below instruction I enter the period and it bring the total invoiced Sub RefreshPivot() Dim asDataSource(2) As String Application.ScreenUpdating = False ' Create Connection String Dim intResult As Integer intResult = GetUserLogin If intResult = 0 Then ' Get Date Entries gsStartDate = frmRefreshDates.txtStartDate.Text gsEndDate = frmRefreshDates.txtEndDate.Text ' Close the dates form frmRefreshDates.Hide ' Hard code the site for now. giSiteID = 1 ' Get to a cell located within the pivot table. ActiveSheet.PivotTables("ONYX1").TableRange1.Selec t ' Get the SQL connection and query strings associated with the pivot table. ' asDataSource = ActiveCell.PivotTable.SourceData asDataSource(1) = strConstring asDataSource(2) = "exec " + strFilename + " " + Str(giSiteID) + "," + Chr$(34) + gsStartDate + Chr$(34) + "," + Chr$(34) + gsEndDate + Chr$(34) On Error GoTo ErrorHandler 'Reset the pivot table data source ActiveSheet.PivotTableWizard xlExternal, asDataSource, ActiveSheet.Range("B8") GoTo Bye Else Exit Sub End If ErrorHandler: MsgBox (Error()) 'MsgBox "Problem retrieving data. It is possible that no rows met your search criteria." Bye: Application.ScreenUpdating = True End Sub "Roger Govier" wrote: Hi Eduardo Post the code you are using -- Regards Roger Govier "Eduardo" wrote in message ... Hi Roger, Thank you for helping me. I was able to create the PV however I have a button that I hit and prompt me to enter the dates I want the information to be reported let's say from 01/07/08 to 31/07/08, and when running this macro it doesn't actualize the new PV, can you help me?, thank you "Roger Govier" wrote: Hi Eduardo Create a second Pivot Table based upon the same data as the first. Say yes to using same source to reduce memory requirements. In the second PT just add Manager to Row area and Margin to Data area. Click on any cell in this second PTPress F11 That will create a Chart for you. Right click on the Chart to select the Type you want -- Regards Roger Govier "Eduardo" wrote in message ... I have a Pivot Table with the information as follow Account Manager Region Total Sales Total Margin What I need is a graphic that contains only this fields Account Manager and Total Margin I was trying to copy the pivot table and select only the fields needed and create the graph from here but I didn't succeed. I need as well that every time I refresh the first pivot table the 2nd one is updated. Thank you in advance for any help |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I Create another pivot table, based on the first one. T
Good morning Roger,
Yes I copy exactly as you send to me. Just I was thinking at night and I found something interesting that maybe it will help If I go to the "Connection Properties" and I copy what is in "Definition" "Command text", and then I go to the new PV and paste it in the Connection properties for this PV it actualize the PV with the right amounts "Roger Govier" wrote: Did you put it in quotes? ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh -- Regards Roger Govier "Eduardo" wrote in message ... Hi Roger, I added the code and it didn't work, however I realized that Onix1 is the name of my PV so I went to the other new PV and as the name was PivotTable2 I change to it in your code, now I get this message error "Unable to get the PivotTable property of the worksheet class" "Roger Govier" wrote: Hi Eduardo Try entering the following line before your Goto Bye ActiveSheet.PivotTables("ONYX1").PivotCache.Refres h -- Regards Roger Govier "Eduardo" wrote in message ... Hi Roger, below is the code, this is an spreadsheet I have received. The company has a system to enter the SO and prepare the invoices, so with the below instruction I enter the period and it bring the total invoiced Sub RefreshPivot() Dim asDataSource(2) As String Application.ScreenUpdating = False ' Create Connection String Dim intResult As Integer intResult = GetUserLogin If intResult = 0 Then ' Get Date Entries gsStartDate = frmRefreshDates.txtStartDate.Text gsEndDate = frmRefreshDates.txtEndDate.Text ' Close the dates form frmRefreshDates.Hide ' Hard code the site for now. giSiteID = 1 ' Get to a cell located within the pivot table. ActiveSheet.PivotTables("ONYX1").TableRange1.Selec t ' Get the SQL connection and query strings associated with the pivot table. ' asDataSource = ActiveCell.PivotTable.SourceData asDataSource(1) = strConstring asDataSource(2) = "exec " + strFilename + " " + Str(giSiteID) + "," + Chr$(34) + gsStartDate + Chr$(34) + "," + Chr$(34) + gsEndDate + Chr$(34) On Error GoTo ErrorHandler 'Reset the pivot table data source ActiveSheet.PivotTableWizard xlExternal, asDataSource, ActiveSheet.Range("B8") GoTo Bye Else Exit Sub End If ErrorHandler: MsgBox (Error()) 'MsgBox "Problem retrieving data. It is possible that no rows met your search criteria." Bye: Application.ScreenUpdating = True End Sub "Roger Govier" wrote: Hi Eduardo Post the code you are using -- Regards Roger Govier "Eduardo" wrote in message ... Hi Roger, Thank you for helping me. I was able to create the PV however I have a button that I hit and prompt me to enter the dates I want the information to be reported let's say from 01/07/08 to 31/07/08, and when running this macro it doesn't actualize the new PV, can you help me?, thank you "Roger Govier" wrote: Hi Eduardo Create a second Pivot Table based upon the same data as the first. Say yes to using same source to reduce memory requirements. In the second PT just add Manager to Row area and Margin to Data area. Click on any cell in this second PTPress F11 That will create a Chart for you. Right click on the Chart to select the Type you want -- Regards Roger Govier "Eduardo" wrote in message ... I have a Pivot Table with the information as follow Account Manager Region Total Sales Total Margin What I need is a graphic that contains only this fields Account Manager and Total Margin I was trying to copy the pivot table and select only the fields needed and create the graph from here but I didn't succeed. I need as well that every time I refresh the first pivot table the 2nd one is updated. Thank you in advance for any help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
| Create interactive pivot table chart based on item selected | Charts and Charting in Excel | |||
| how do I create a pivot table based on multiple sheets | Excel Discussion (Misc queries) | |||
| How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel | |||
| How to create a calculated field formula based on Pivot Table resu | Excel Discussion (Misc queries) | |||
Linear Mode
