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)?

Component	Amount
C-100	4
C-10	5
P-150	7
C-100	3
P-100	2
C-100	4

<tbody>
</tbody>

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

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

<tbody>
</tbody>

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!