Copy data from pivot table with blank data

**triaji** · 02-19-2015, 11:15 PM

Hi,
I want to copy data row from pivot table to another sheet. Sometimes the pivot table has a data and sometimes blank (Dynamic).

The problem is when the pivot table has no data the macro cannot run, its said: "Run error '1004' the formula is not complete. Make sure an ending square bracket ] is not missing"

I'm using this macro:

PHP Code:


Sheets("PIVOT Agent Financing").Select 
    ActiveSheet.PivotTables("PivotTable2").PivotSelect "'Kode Agent'[All]", _ 
        xlLabelOnly, True 
    Selection.Copy 
    Sheets("2.SYC FINANCING").Select 
    Range("A2").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False

Please help, Thanks In Advance.

**MarvinP** · 02-19-2015, 11:24 PM

Hi triaji,

This sounds like you simply need to double click on the cell in the Pivot Table that would create a new sheet showing the data that made up that number. This is called a Drill Down maneuver. Read about it at:

http://chandoo.org/wp/2010/01/27/pivot-table-tricks/

If you need VBA then Record a macro when you do the above and see what the VBA says you did.