excel vba how to filter each pivot item and copy paste to another sheet?

[huiyin9218]

Hi,

May I know how to write a code to automatically filter each component types (C type, P type) available in my data, copy paste to their respective sheet and arrange according to their component names (C-100, C-10, etc)?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Component[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]C-100[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]C-10[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]P-150[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]C-100[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]P-100[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C-100[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

Table above is a example of my data. I would like to copy all C components and its amount and paste it at sheet C. In sheet C, all C components will arrange according to their component name, shown in table below

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Component Name[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD]Component Name[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]C-100[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]C-10[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]C-100[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C-100[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Component P will be arrange like component C but it has to be in sheet P

I would be so grateful for your help.

 

[fhqwgads]

maybe something like this?

Sub selectiontest1()

Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)

pt.PivotFields("Component").PivotFilters. _
        Add Type:=xlCaptionContains, Value1:="C"
pt.DataBodyRange.Cells(pt.DataBodyRange.Rows.Count, _
    pt.DataBodyRange.Columns.Count).ShowDetail = True

pt.PivotFields("Component"). _
        ClearLabelFilters

pt.PivotFields("Component").PivotFilters. _
        Add Type:=xlCaptionContains, Value1:="P"
pt.DataBodyRange.Cells(pt.DataBodyRange.Rows.Count, _
    pt.DataBodyRange.Columns.Count).ShowDetail = True

pt.PivotFields("Component"). _
        ClearLabelFilters

End Sub

the C sheet looks like this

Component	Amount
C-10          	5
C-100        	4
C-100        	4
C-100       	3

i couldnt get the separate components to display left to right like you wanted

 

[huiyin9218]

maybe something like this?

Sub selectiontest1()

Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)

pt.PivotFields("Component").PivotFilters. _
        Add Type:=xlCaptionContains, Value1:="C"
pt.DataBodyRange.Cells(pt.DataBodyRange.Rows.Count, _
    pt.DataBodyRange.Columns.Count).ShowDetail = True

pt.PivotFields("Component"). _
        ClearLabelFilters

pt.PivotFields("Component").PivotFilters. _
        Add Type:=xlCaptionContains, Value1:="P"
pt.DataBodyRange.Cells(pt.DataBodyRange.Rows.Count, _
    pt.DataBodyRange.Columns.Count).ShowDetail = True

pt.PivotFields("Component"). _
        ClearLabelFilters

End Sub

the C sheet looks like this

Component    Amount
C-10              5
C-100            4
C-100            4
C-100           3

i couldnt get the separate components to display left to right like you wanted

Its okay. Your way is much easier. Thank you!