I have a pivot table that I create from a time reporting report I receive weekly. The report is the the entire company so I need to filter out all employees except my own. There are about 100 employees in the report but I only have 12. I recorded a macro that does this but the problem is if anyone leaves I get an error because that value is no longer there for the code to hide it. I can work around that by simply telling it to move on when there's an error. But that only masks the issue. The second problem is when a new person is added to the company. I now would have to identify the new person, add them to the code and run it again... Therem ust be a more dynamic way to hide everyone and then unhide or make visible my 12 people.
Even better would be for the code to look at a seperate excel file or .txt file for valid values and if the value is in the file then make that value visible in the pivot table. For now I will settle for a hide everything and unhide values I hardcode in the code. Here is a snipit of what I am doing.
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Resource Name")
.PivotItems("A R A").Visible = False
.PivotItems("Abhilash").Visible = False
.PivotItems("Abrol").Visible = False
.PivotItems("Angadipeta").Visible = True
.PivotItems("Angra").Visible = False
Again, there are at least a hundred I need to hide and only 12 i need visible. Manually adding and removing the ones I need to hide will be very timeconsuming.
I'm using excel 2010
Even better would be for the code to look at a seperate excel file or .txt file for valid values and if the value is in the file then make that value visible in the pivot table. For now I will settle for a hide everything and unhide values I hardcode in the code. Here is a snipit of what I am doing.
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Resource Name")
.PivotItems("A R A").Visible = False
.PivotItems("Abhilash").Visible = False
.PivotItems("Abrol").Visible = False
.PivotItems("Angadipeta").Visible = True
.PivotItems("Angra").Visible = False
Again, there are at least a hundred I need to hide and only 12 i need visible. Manually adding and removing the ones I need to hide will be very timeconsuming.
I'm using excel 2010