PDA

View Full Version : Unable to set the visible property of the pivot item class



Emily2
07-03-2015, 01:35 AM
Hi

I have the following code, it keeps coming up with error 1004 'unable to set the visible property of the pivot item class' Any ideas?

The code exports and filters data on the pivot table by principle investigator name to a new sheet.

Sub CopyPivDataPI()

Dim PT As PivotTable
Dim PI As PivotItem
Dim PI2 As PivotItem

'1)Worksheet name where PIVOT Table is located
MyWs = "Monthly Summary"
'2)PIVOT table name/number, note by default the first one created is PivotTable1
MyPIV = "PivotTable1"
'3)Field Name that you want to use for breaking out by, i.e. the filter name
MyField = "Principle investigator"

Set PT = Worksheets(MyWs).PivotTables(MyPIV)
With PT

For Each PI In Worksheets(MyWs).PivotTables(MyPIV).PivotFields(MyField).PivotItems
PI.Visible = True

For Each PI2 In Worksheets(MyWs).PivotTables(MyPIV).PivotFields(MyField).PivotItems
If Not PI2.Name = PI.Name Then PI2.Visible = False
Next PI2
Set NewWs = Worksheets.Add
NewWs.Name = PI & " " & Format(Date, "mmm-yy")




'You will need to amend the range below to copy the correct amount of data for your file
Worksheets(MyWs).Range("A1:Z345").Copy

'This pastes into cell A1 of the new sheet
NewWs.Range("A1").Select
ActiveSheet.Paste
Next PI



Application.ScreenUpdating = False
Dim wkSt As String
Dim wkBk As Worksheet
wkSt = ActiveSheet.Name
For Each wkBk In ActiveWorkbook.Worksheets
On Error Resume Next
wkBk.Activate
Cells.EntireColumn.AutoFit
Next wkBk
Sheets(wkSt).Select
Application.ScreenUpdating = True
End With
End Sub

mancubus
07-04-2015, 12:30 AM
as an experienced vbax member you should have pasted your code in between code tags.

[ CODE ]Paste your code here and remove spaces before and after brackets[ /CODE ]

can you post your workbook for a better understanding of the sheet and pivot table structure?