Problem copying a column from Pivot Table in same sheet

[subrahmanyam85]

I am trying to copy a column from pivot table into another column with unique values in same sheet.Pivot table header is in A6 to F6.

That column contains Year values and there are no filters on it.

While executing the below code it is showing an error like "Application Defined or Object Defined Error"

===============================================================
Private Sub GetUnique()

Dim LR As Long

LR = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

Range("A7:A" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("H7"), Unique:=True

End Sub
===============================================================

Can you please help me?

 

[Andrew Poulsom]

What headings do you have in H6:I6? Also you seem to have a couple of typos in your VBA code:

Range("B6:B" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("I6"), Unique:=True
LR = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
Range("C6:C" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("J6"), Unique:=True

 

[subrahmanyam85]

There are no heading in G,H,I... (empty) columns.

I am getting "Application defined or Object defined error" with your recent code.

 

[Andrew Poulsom]

What happens if you copy A6:C6 and paste into H6 and then run your code?

 

[subrahmanyam85]

Hi Andrew,

Its working thank you.

All my struggles are to show the data of Pivot Table in below(Requirement Table) format by using VBA Arrays.For that I amt taking distinct values in to array.
Below the first table is pivot data and the second one is my requirement.

Here Year,Reporting Year,Code column values will vary based on the pivot table.Currency column values are in AUD,JPY,USD.
Column E6 value should comes as row header.These values also fixed.

I have tried in many ways...but no luck...Please suggest me a best way to achive this...

Pivot Table
========
[TABLE="width: 741"]
<tbody>[TR]
[TD]Year(A6)[/TD]
[TD]Reporting Year(B6)[/TD]
[TD]Code(C6)[/TD]
[TD]Currency(D6)[/TD]
[TD]Usage (E6)[/TD]
[TD]Amount(F6)[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]RY-2011[/TD]
[TD]1[/TD]
[TD]USD[/TD]
[TD]Excel Claims Handling[/TD]
[TD]6.52[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]RY-2012[/TD]
[TD]NPC[/TD]
[TD]JPY[/TD]
[TD]Claims Handling Provision[/TD]
[TD]130.79[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]RY-2011[/TD]
[TD]KPY[/TD]
[TD]AUD[/TD]
[TD]Unexpired Risk Provision[/TD]
[TD]-182.91[/TD]
[/TR]
[TR]
[TD]2008[/TD]
[TD]RY-2011[/TD]
[TD]NPC[/TD]
[TD]USD[/TD]
[TD]Outstanding Claims[/TD]
[TD]0.71[/TD]
[/TR]
[TR]
[TD]2009[/TD]
[TD]RY-2009[/TD]
[TD]YMC[/TD]
[TD]USD[/TD]
[TD]URisk Provision[/TD]
[TD]-655.21[/TD]
[/TR]
[TR]
[TD]2010[/TD]
[TD]RY-2011[/TD]
[TD]CC-12[/TD]
[TD]USD[/TD]
[TD]Unexpired Risk Provision[/TD]
[TD]-2260.16[/TD]
[/TR]
[TR]
[TD]2008[/TD]
[TD]RY-2011[/TD]
[TD]NPC[/TD]
[TD]AUD[/TD]
[TD]Other Claims[/TD]
[TD]90.5[/TD]
[/TR]
[TR]
[TD]2008[/TD]
[TD]RY-2011[/TD]
[TD]NPC[/TD]
[TD]AUD[/TD]
[TD]Other Claims[/TD]
[TD]5.5[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]


Requirement Table
============
[TABLE="width: 939"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Reporting Year[/TD]
[TD]Code[/TD]
[TD]Currency[/TD]
[TD]Excel Claims Handling[/TD]
[TD]Unexpired Risk Provision[/TD]
[TD]Outstanding Claims[/TD]
[TD]Other Claims[/TD]
[TD]URisk Provision[/TD]
[TD]Unexpired Risk Provision[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]RY-2011[/TD]
[TD]1[/TD]
[TD]USD[/TD]
[TD]6.52[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]RY-2011[/TD]
[TD]KPY[/TD]
[TD]AUD[/TD]
[TD]0[/TD]
[TD]-182.91[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]RY-2012[/TD]
[TD]NPC[/TD]
[TD]JPY[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2008[/TD]
[TD]RY-2011[/TD]
[TD]NPC[/TD]
[TD]USD[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0.71[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2008[/TD]
[TD]RY-2011[/TD]
[TD]NPC[/TD]
[TD]AUD[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]96[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2009[/TD]
[TD]RY-2009[/TD]
[TD]YMC[/TD]
[TD]USD[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]-655.21[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2010[/TD]
[TD]RY-2011[/TD]
[TD]CC-12[/TD]
[TD]USD[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]-2260.16[/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="2"><col><col><col><col><col></colgroup>[/TABLE]

 

[Andrew Poulsom]

Why don't you just move Usage to the column area in your pivot table?

 

[subrahmanyam85]

Yes,We can do it like that. But it should not be pivot table and structure also should be same.

Now what I am trying to do is that from pivot table I will get the distinct rows of "Year,Reporting Year,code,currency".Later, using excel function i will check the values of "Unexpired Risk Provision,Outstanding Claims,Other Claims,URisk Provision".

In this process I am trying to get the distinct rows from A7 to D7 from pivot table by using VBA.

LR = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Range("A6:D" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("J6"), Unique:=True

It is not working.

 

[Andrew Poulsom]

You can use GETPIVOTDATA to extract the values. Here's how:

http://www.contextures.com/xlPivot06.html