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
========

Year(A6)	Reporting Year(B6)	Code(C6)	Currency(D6)	Usage (E6)	Amount(F6)
2003	RY-2011	1	USD	Excel Claims Handling	6.52
2003	RY-2012	NPC	JPY	Claims Handling Provision	130.79
2003	RY-2011	KPY	AUD	Unexpired Risk Provision	-182.91
2008	RY-2011	NPC	USD	Outstanding Claims	0.71
2009	RY-2009	YMC	USD	URisk Provision	-655.21
2010	RY-2011	CC-12	USD	Unexpired Risk Provision	-2260.16
2008	RY-2011	NPC	AUD	Other Claims	90.5
2008	RY-2011	NPC	AUD	Other Claims	5.5

<tbody>
</tbody><colgroup><col><col><col><col><col><col></colgroup>

Requirement Table
============

Year	Reporting Year	Code	Currency	Excel Claims Handling	Unexpired Risk Provision	Outstanding Claims	Other Claims	URisk Provision	Unexpired Risk Provision
2003	RY-2011	1	USD	6.52		0	0	0	0
2003	RY-2011	KPY	AUD	0	-182.91	0	0	0	0
2003	RY-2012	NPC	JPY	0	0	0	0	0	0
2008	RY-2011	NPC	USD	0	0	0.71	0	0	0
2008	RY-2011	NPC	AUD	0	0	0	96	0	0
2009	RY-2009	YMC	USD	0	0	0	0	-655.21	0
2010	RY-2011	CC-12	USD	0	0	0	0	0	-2260.16

<tbody>
</tbody><colgroup><col><col><col><col span="2"><col><col><col><col><col></colgroup>

 

[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