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