Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer pivot table data to another workbook
Hi
I'm trying to transfer data from a pivot table in the following format: Order Number Activity Code Time 400500 Z1 0:30 400600 X1 0:15 P3 0:15 400800 A1 0:45 BL2 0:30 CF1 0:15 to another workbook in this format: Order Number Activity Code Time 400500 Z1 0:30 400600 X1 0:15 400600 P3 0:15 400800 A1 0:45 400800 BL2 0:30 400800 CF1 0:15 I've tried working with rowfields, datarange, rowrange, but all attempts so far have failed. Dim pt as pivottable dim pf as pivotfield dim rng as range for each pf in pt.rowfields set rng = pf.datarange debug.print pf for each cell in rng if not isempty(cell) then debug.print cell next next The above goes through the row fields Order Number and Activity Code. How can I grab the Order Number when it is not shown in the table, eg 400600 for P3? Also, how can I get the Time (data field) at the same time? Ideally I would like to iterate through each row of the pivot table, get the data from all 3 fields and transfer it to a new workbook. Thanks Tony |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer pivot table data to another workbook
Dave Peterson wrote:
Copy|paste special|values (the copy won't be a pivottable anymore). Then use one of the techniques at Debra Dalgleish's site to fill in those empty cells: http://contextures.com/xlDataEntry02.html Hi Thanks for your help on this. I recorded a macro to copy and paste the pivot table then tried to amend it to get the data I required. I could get some of the data but not all at the same time. It was all getting a bit messy. In the end I found another way: x = pt.RowRange gives an array of values in the row area (Order no. & Activity Code) y = pt.DataBodyRange gives an array of values in the data area (Time) Then it was easy to iterate through the arrays copying the values to the other workbook. Also thanks for the link, I've used your FillColBlanks routine to fill the blank Order numbers. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer pivot table data to another workbook
If that pivottable is on a dedicated worksheet with nothing else, you could just
copy the cells: dim ActWks as worksheet dim newWks as worksheet set actwks = worksheets("sheet1") set newwks = worksheets.add actwks.usedrange.copy newwks.range("a1").pastespecial paste:=xlpastevalues If you had to pick out one pivottable... Dim ActWks As Worksheet Dim newWks As Worksheet Set ActWks = Worksheets("sheet1") Set newWks = Worksheets.Add ActWks.PivotTables(1).TableRange1.Copy newWks.Range("a1").PasteSpecial Paste:=xlPasteValues But glad you found a way for it to work. Tony James wrote: Dave Peterson wrote: Copy|paste special|values (the copy won't be a pivottable anymore). Then use one of the techniques at Debra Dalgleish's site to fill in those empty cells: http://contextures.com/xlDataEntry02.html Hi Thanks for your help on this. I recorded a macro to copy and paste the pivot table then tried to amend it to get the data I required. I could get some of the data but not all at the same time. It was all getting a bit messy. In the end I found another way: x = pt.RowRange gives an array of values in the row area (Order no. & Activity Code) y = pt.DataBodyRange gives an array of values in the data area (Time) Then it was easy to iterate through the arrays copying the values to the other workbook. Also thanks for the link, I've used your FillColBlanks routine to fill the blank Order numbers. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transfer data from an excel tab to a pivot table automatically | Excel Discussion (Misc queries) | |||
How to transfer data from one workbook to another via VBA | Excel Discussion (Misc queries) | |||
How do I transfer data with hyperlinks to a pivot table without l. | Excel Worksheet Functions | |||
Data transfer from a template to a workbook | Excel Worksheet Functions | |||
How can I transfer data from excel worksheet into pivot table? | Excel Programming |