How do I Create another pivot table, based on the first one. Thank

[Eduardo]

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

 

[Roger Govier]

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 PT>Press F11
That will create a Chart for you. Right click on the Chart to select the
Type you want

 

[Eduardo]

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]

Hi Eduardo
Post the code you are using

 

[Eduardo]

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

' 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]

Hi Eduardo

Try entering the following line before your Goto Bye

ActiveSheet.PivotTables("ONYX1").PivotCache.Refresh

 

[Eduardo]

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]

Did you put it in quotes?
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh

 

[Eduardo]

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