Copy and Paste PivotTable data

[Fern]

I need to grabe pivottable data with 4 fields to another worksheet, where data have to be update and place horizontally with repeated heading fields, as long as there is available column still fit to the page. The reason is to fillup the page with all datas instead of having only occupied 3columns in 1page which eventually more pages created.

Thank you very much for your time n reply.

[Jay Petrulis]

Hi,

I created a PivotTable called "PivotTable1" and copied the values only to a new sheet "Sheet4"), in the next available column (row 1 as well)

Sub test()
Dim lastcol As Integer

ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
    Selection.Copy
With Sheets("Sheet4")
.UsedRange
lastcol = .Cells.SpecialCells(xlCellTypeLastCell).Column
.Cells(1, lastcol + 1).PasteSpecial (xlValues)
End With

End Sub

I am unsure if this will address your request, so please try this and report your results.

If you need to copy the "live" pivot table, it will require a bit of adjustment to the code.

Thanks,
Jay

[Fern]

Thanks and I did run your codes successfully. Yes, it is a 'live' PivotTable.

Because I set the pagesetup Scaling-Adjuct to 60% normal size, I have in Sheet4 cell A..P in 1 page. Now that in PivotTable I have only 3 column fields(eg. Name, Group, Quantity) with more than hundreds rows of data. I do not want Sheet4 having only used 3columns with hundreds rows of data in many many pages. I want to occupied all columns in Sheet4.
In Sheet4, columnA and ColumnD will have Name Data, columnB and columnE Group Data, columnC and columnF Quantity Data, and so forth. Of course here we need to update Sheet4 data row by row.

[Fern]

I need to run this on Excel97, please. Thanks for your initial code too, cos I did learn something new. Please again try to understand my request, and I shall detail explain if necessary.