PDA

View Full Version : paste special values for multiple pivots



sravanmonty
01-25-2017, 12:51 PM
Hello Experts

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

mana
01-27-2017, 04:30 AM
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