Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default 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.


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default 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.
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default Pivot table - VB code

please please See this link for my PT and sheet.


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


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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


  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default 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.

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Code For Pivot Table. Heera Excel Programming 1 October 25th 08 12:58 PM
Pivot Table Code - Simple Code Fix? J.W. Aldridge Excel Programming 2 April 4th 08 02:24 PM
Code that will rerun or refresh a pivot table (after new data ispasted into the original Pivot Table's Source Range) Mike C[_5_] Excel Programming 3 February 15th 08 07:22 AM
Code for a Macro that allows a Pivot table to be repeatedly run (fora dynamic rows in a table, but static field names) Mike C[_5_] Excel Programming 1 February 11th 08 05:30 AM
VBA Code for a pivot table to open database and to reference table current page Pete Straman Straman via OfficeKB.com Excel Programming 0 February 21st 05 04:57 AM


All times are GMT +1. The time now is 09:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"