please help me,
I cant attach files to clarify the issue

is there a way to list pivot table data to a normal sheet rows and columns

this is my daily work it took me 1 hour to finish it , but I think with a VB code it could be done on 1 minute.

I don't know if this could be done by formula.



please reply to me for any clarification.

"TheExpat Wanderer" wrote in message
please help me,
I cant attach files to clarify the issue

is there a way to list pivot table data to a normal sheet rows and columns

this is my daily work it took me 1 hour to finish it , but I think with a
VB code it could be done on 1 minute.

I don't know if this could be done by formula.
please reply to me for any clarification.

If you mean copy the entire PT as displayed including labels

Sub test()
Dim pt As PivotTable
Dim wsSource As Worksheet
Dim wsTarget As Worksheet

Set wsSource = Worksheets("Sheet1") ' < change
Set wsTarget = Worksheets("Sheet2") ' < change

Set pt = wsSource.PivotTables(1)

pt.TableRange2.Copy

With wsTarget.Range("A1")
.PasteSpecial xlPasteValues
' following are optional
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteFormats
End With

End Sub

Regards,
Peter T

If either sheet might not be in the active workbook qualify as necessary, eg

Set ws = Worbooks("filename").Worksheets("Sheetname")

Regards,
Peter T

thank You Peter T for your reply.
I want to list specific rows and columns , not all.

for example if I filter the table I can copy the rows 5 and 6 to a different sheet in row 1 and 2
then filter again the vb code will copy the same rows to the same different sheet in row 3 and 4.

I do this manually every day to copy and past over 30 rows to new sheet.
please assist.

note that : there's multiple filter criteria.

"TheExpat Wanderer" wrote in message
thank You Peter T for your reply.
I want to list specific rows and columns , not all.

for example if I filter the table I can copy the rows 5 and 6 to a
different sheet in row 1 and 2
then filter again the vb code will copy the same rows to the same
different sheet in row 3 and 4.

I do this manually every day to copy and past over 30 rows to new sheet.
please assist.

note that : there's multiple filter criteria.

You didn't explain that in your OP and without a clear description, or
seeing your Pivot Table and knowing the combination of filters and actions
with the multiple criteria, it's hard to suggest a generic solution.

If I follow you've also added a new requirement to paste below what was
pasted last time which is a separate matter.

Regards,
Peter T

please please See this link for my PT and sheet.


https://groups.google.com/d/msg/exce...I/aU0AsgMYBQAJ

"TheExpat Wanderer" wrote in message
please please See this link for my PT and sheet.


https://groups.google.com/d/msg/exce...I/aU0AsgMYBQAJ

I've looked at your screenshots but they don't explain the steps taken or
detail needed to given you any sort of solution.

Because you know what you want to do maybe you can work things out yourself.
In the macro I suggest put a break just after this line (or step through
with F8)

Set pt = wsSource.PivotTables(1)

Look at 'Locals', Alt-v, s, and expand pt. You will see the entire object
model of the PivotTable available to you. Instead of copying TableRange2,
look at various other PT Range objects available and copy those instead the
same way, maybe one or more of those will be what you are looking for.

A different approach might be to "Find" the top and bottom labels in the
left column of the PT you're interested in together with the column
intersect. With those details you may be able to reference the particular
area of the PT you want to copy.

Regards,
Peter T