Changing a pivot table field items to current database

[Pete Straman Straman via OfficeKB.com]

I recorded a macro that included showing a pivot field item.
I have to run the macro on different databases that have a unique field
item. There is only one field name in the field in a drop down box. How can
I change the code below to show the field item that is established when
creating the pivot table not the one that was created when I recorded macro

ActiveSheet.PivotTables("PivotTable1").PivotFields("facilityid")
..CurrentPage = "60172"

When I run the macro on the next database in the current page maybe 60173,
60174, ... My macro will get a run time error because it can not find the
60172 item.

Hope this makes some sense and someone can help.

Thanks in advance.

C. Pete Straman

 

[Tom Ogilvy]

Do you mean "ALL"

 

[Debra Dalgleish]

If you need to show the item as selected in the dropdown:

With ActiveSheet.PivotTables(1).PivotFields("facilityid")
.CurrentPage = .PivotItems(1).Value
End With

 

[Pete Straman Straman via OfficeKB.com]

Hey!

Thanks for the help. At first the code did not work and then it did. It
works for PivotTable1 but not for PivotTable2. Table 2 is a copy of table 1
with column data added.
I tried:

With ActiveSheet.PivotTables(2).PivotFields("facilityid")
.CurrentPage = .PivotItems(1).Value
End With

with out success.

Pete

PS - I am goin to be posting all my code soon to ask for help related to
not being able to run the program from start to finish twice without a run
time error. I to complete exit Excel and open in Book1 to get it to work. I
have removed all references to Book1 but it still crashes.
Just in case you have comments here it is.

'***** represent problem areas

Sub Macro_Testing()

'
' Create_Pivot_Table Macro
' Macro recorded 2/19/2005 by Pete Straman
'
' Keyboard Shortcut: Ctrl+Shift+T

'*******Tried this stuff to if deleting a sheet and adding it would help
run time error on second time thru program

' Delete PivotSheet if it exists
'On Error Resume Next
'Application.DisplayAlerts = False
'Sheets("Sheet1").Delete
'On Error GoTo 0

' Add new worksheet
'Worksheets.Add
'ActiveSheet.Name = "Sheet1"

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = "ODBC;DSN=MS Access Database;"
.CommandType = xlCmdSql
.CommandText = "SELECT trend_rpt.facilityid, trend_rpt.`Sum of
Revenue`," _
+ " trend_rpt.`Sum of Payments`, trend_rpt.`Sum of Adjustments`,
trend_rpt.transmoyr," _
+ " trend_rpt.dosmoyr" & Chr(13) & "" & Chr(10) & "FROM trend_rpt
trend_rpt" _

' *************************How do I set this to a specified directory?
****************
' *****************tried at .Connection without success

.CreatePivotTable TableDestination:="Sheet1!R1C1", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

'************************************************************************************
End With

ActiveSheet.PivotTables("PivotTable1").ColumnGrand = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="dosmoyr", _
PageFields:="facilityid"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of
Revenue")
.Orientation = xlDataField
.Caption = "Revenue"
.NumberFormat = "$#,##0.00_);($#,##0.00)"
End With
Range("A5:A65").Select

Selection.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=6,
_
Orientation:=xlTopToBottom

With ActiveSheet.PivotTables(1).PivotFields("facilityid")
.CurrentPage = .PivotItems(1).Value
End With

Columns("A:B").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.PivotTableWizard TableDestination:="Sheet1!R1C3"



'******* This statement will cause a runtime error used in
'******* anything but Book1 - so I have to close and reopen excel to
get it to run
' Run time error 1004 unable to get the Pivot Tables property of the
worksheet class

ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="dosmoyr", _
ColumnFields:="transmoyr", PageFields:="facilityid"

'*********************************************************************************
'*********************************************************************************

With ActiveSheet.PivotTables(2).PivotFields("facilityid")
.CurrentPage = .PivotItems(1).Value
End With

ActiveSheet.PivotTables("PivotTable2").PivotFields("Revenue")
..Orientation = _
xlHidden
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of
Payments")
.Orientation = xlDataField
.Caption = "Payments"
.NumberFormat = "$#,##0.00_);($#,##0.00)"
End With
ActiveSheet.PivotTables("PivotTable2").DataPivotField.PivotItems
("Payments"). _
Position = 1
Columns("C:C").Select
Selection.EntireColumn.Hidden = True


Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pivot Table"
Sheets("Pivot Table").Select
Sheets("Pivot Table").Copy Before:=Sheets(1)
Sheets("Pivot Table (2)").Select
Sheets("Pivot Table (2)").Name = "Collections"
Cells.Select
Selection.Copy
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Collections").Select
Application.CutCopyMode = False
Sheets("Collections").Move Before:=Sheets(3)
Sheets("Pivot Table").Select

End Sub

 

[Pete Straman Straman via OfficeKB.com]

Yes,

I meant All. But to get it off of ALL to the value below it. I have code
now that fixes PT1 but not PT2.
Still working on it. See my reply to posting help.
Thank you in advance for anything you have to offer.

Pete