Pivot table - VB code
 

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.

Pivot table - VB code
 

"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

Pivot table - VB code
 

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.

Pivot table - VB code
 

"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

Pivot table - VB code
 

please please See this link for my PT and sheet.


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

Pivot table - VB code
 

"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

Pivot table - VB code
 

thank you again.
ill make my question very easy. and it will do what I want


Firstly , Cell B1 will take the value from A1
then if A1 value changed , it's value will be in Cell B2.(B1 will not change)
if A1 changed again ,it's value will be in Cell B3.(B1,B2 will not change)
if A1 changed Again ,it's value will be in Cell B4.(B1,B2,B3 will not change)

and so on

thank you in advance.

Pivot table - VB code
 

"TheExpat Wanderer" wrote in message
thank you again.
ill make my question very easy. and it will do what I want


Firstly , Cell B1 will take the value from A1
then if A1 value changed , it's value will be in Cell B2.(B1 will not
change)
if A1 changed again ,it's value will be in Cell B3.(B1,B2 will not change)
if A1 changed Again ,it's value will be in Cell B4.(B1,B2,B3 will not
change)

and so on

thank you in advance.

I'm sorry but I cannot relate any of the above to your screenshots. Did you
try the original macro and then did you try what I suggested last time?

If anybody else with a different set of eyes can follow please jump in :)

Regards,
Peter T