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

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

wrote:

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

--

Dave Peterson

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.

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