paste special values for multiple pivots

[sravanmonty]

Hello Experts

How to paste special values for multiple pivots in a single sheet without loosing it format with excel vba.

[mana]

Option Explicit


Sub test()
Dim wsPivot As Worksheet
Dim wsNew As Worksheet
Dim pvt As PivotTable
Dim rng As Range

Set wsPivot = ActiveSheet
Set wsNew = Worksheets.Add
Set rng = wsNew.Range("a1")

For Each pvt In wsPivot.PivotTables
With pvt.TableRange2
.Copy
rng.PasteSpecial Paste:=xlPasteValues
rng.PasteSpecial Paste:=xlPasteFormats
Set rng = rng.Offset(.Rows.Count + 2)
End With
Next

End Sub