Pivot Table Drillthrough

[arom]

I have a pivot table based on data that has about 10 columns. I was just wondering if it's possible that when I drill through the table, I will only see some of those columns. Basically, can I control the drillthrough or will I always see all the columns of the data source?

Hope this makes sense. Thanks in advance.

 

[schielrn]

The short answer is that you will always see all the columns. You can use some vb code to make it hide or delete columns, but that can get complicated. But there isn't a built-in feature to only show the columns or data that is on the pivot table unfortunately.

 

[arom]

That's what I thought- thanks for your response.

What you are suggesting, I found someone's code that does exactly that:

Private Sub Worksheet_Deactivate()
If Left(ActiveSheet.Name, 5) = "Sheet" Then
ActiveSheet.Range("O").Delete
ActiveSheet.Range("M").Delete
ActiveSheet.Range("B:F").Delete
End If
End Sub

But I put this in "ThisWorkbook" (I assumed that's where it goes) and it doesn't work for me.. Those columns do not get deleted when I drill down.. any thoughts?

Thanks again.

 

[schielrn]

It needs to go in the pivot table sheet code and it will delete data on any sheet that begins with "Sheet" just as a caution.

Hope that helps.

 

[arom]

Ah! I just saw that it's your code from another post! Sorry, didn't realize before... Anyway- it doesn't work for me.... Did this work for you?

 

[arom]

Ok I'm putting the code on the right sheet- because it's calling the macro. However, I am getting an error. this is the code:

Private Sub Worksheet_Deactivate()
If Left(ActiveSheet.Name, 5) = "Sheet" Then
ActiveSheet.Range("B").Delete
End If
End Sub


It's highlighting the 3rd row (activesheet...).

Not sure why.

 

[schielrn]

Try this:

Private Sub Worksheet_Deactivate()
If Left(ActiveSheet.Name, 5) = "Sheet" Then
ActiveSheet.Columns("B").Delete
End If
End Sub

Or:

Private Sub Worksheet_Deactivate()
If Left(ActiveSheet.Name, 5) = "Sheet" Then
ActiveSheet.Range("B1").entirecolumn.Delete
End If
End Sub

If you do delete more than one column make sure you work from right to left with deleting the columns like the first set of code. Hope that helps.

 

[arom]

that first one worked!!! thanks so much!