Problem copying a column from Pivot Table in same sheet

subrahmanyam85

New Member
Joined
Aug 26, 2014
Messages
20
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?
 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Format of my pivot table is like below.H6 will be empty cell.
ABCDEFGH
6YearReportCodeCategoryItemAmount
71994RY-2011TRTGItem10.00
81995RY-2011ACTGItem20.00
91995RY-2011ACTGItem33,061.83
101996RY-2011GHTGItem40.00
111996RY-2011GHTGItem5653.63
12..............
13............

<tbody>
</tbody><colgroup><col><col><col><col><col><col span="4"></colgroup>
 
Upvote 0
Put a heading Year in H6. Then try:

Code:
Private Sub GetUnique()
    Dim LR As Long
    LR = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    Range("A6:A" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("H6"), Unique:=True
End Sub
 
Upvote 0
When I am trying to filter the data of pivot table, it is giving an error like 'AdvancedFilter method of range class failed'.Can you please help me out? :(
 
Upvote 0
Hi Andrew,

Thank alot for your reply and sorry for my late response.

I am having the filters on Column 'E'.
Please refer pivot table structure on earlier reply and below is my VBA Script.


=====================================================
Private Sub GetUnique()
Dim LR As Long
LR = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Range("A6:A" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("H6"), Unique:=True
LR = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
Range("B6:A" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("I6"), Unique:=True
LR = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
Range("C6:A" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("J6"), Unique:=True
End Sub
=====================================================
 
Upvote 0
Which line causes the error? Do you have a heading in the CopyToRange that matches the heading in the range you want to filter? It may help to see a sample of your data.
 
Upvote 0
Currency View Name,Reporting Category,Date Hierarchy,Category Group are filters in pivot table.

I am also filtering few the column Usage (E6). I have mentioned the column and row info in brackets.


Currency View Name[Multiple Items]
Reporting Category1132
Date HierarchyP12
Category GroupUKG
Year(A6)Reporting Year(B6)Code(C6)Currency(D6)Usage (E6)Amount(F6)
2003RY-20111USDExcel Claims Handling 6.52
2003RY-2011NPCUSDExcel Claims Handling Provision130.79
2003RY-2011KPYUSDUnexpired Risk Provision-182.91
2008RY-2011NPCUSDOutstanding Claims0.71
2009RY-2011YMCUSDURisk Provision-655.21
2010RY-2011CC-12USDUnexpired Risk Provision-2,260.16

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

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top