pivot table clear data in cells

[memory100]

I have pivot table set up in report format with various fields from a CRM database.

I want to clear blank data in the cells when the user click in the pivot table cell to open a certain cell to view the data that is whtiin the pivot table. it creates a new sheet with all the data in but i only want it show the data the user wants to see as it shows me so many blank data fields!

here is screenshot of the pivot table:
http://img811.imageshack.us/img811/5184/69095741.jpg

new sheet that is created when the user click the pivit table cell they want to see:
http://img130.imageshack.us/img130/6124/united2yt.jpg

i have tried to use a macro to clear the cells in the new sheet the pivot tables creates but the data range can vary accoridng to the users selection of data from the pivot table is there any way to sort this out ?

here is the code for the macro i have used:

Sub Test()
    With ActiveSheet.Range("A1:A5").CurrentRegion
        .AutoFilter
        .AutoFilter Field:=4, Criteria1:="="
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .Parent.AutoFilterMode = False
    End With
End Sub

could i use vb to solve this ? or is a macro best for this?

question is it possible to create a macro that can rid of these range of fields ? as the range will differ according to what cell the user opens in the pivot table as the data us pulled and refreshed from the datasource.