CODEWITHSUNDEEP
vbaexcel
ERKSMTY
ERKSMTY

Reputation: 135

Copy Pivot Table and Paste Without Pivot Table Formatting

I have a simple macro that copies a pivot table from one tab to another. Any idea how to modify so the pivot table is pasted as a standard table? Thanks :)

Sub Five_Felicia_For_MFG()
Sheets("5Felicia").Range("A1:M1000").Copy Destination:=Sheets("5Felicia for MFG").Range("A1")

Sheets("5Felicia for MFG").Columns("A:M").AutoFit

End Sub

Upvotes: 0

Views: 1784

Answers (1)

Amorpheuses
Amorpheuses

Reputation: 1423

If you do it as a PasteSpecial command using something like this, it should work:

Sub copy_pivot_table()
  Sheets("5Felicia").Range("A1:M1000").Copy
  Sheets("5Felicia for MFG").Range("A1:M1000").PasteSpecial xlPasteValues
End Sub

EDIT:

If you want to turn it into a regular table (the above just gives you values on the sheet) you can do this:

Sub copy_pivot_table()
  Dim objTable As ListObject
  Dim srcRng As Range, dstRng As Range

  Set srcRng = Sheets("5Felicia").Range("A1:M1000")
  Set dstRng = Sheets("5Felicia for MFG").Range("A1:M1000")

  srcRng.Copy
  dstRng.PasteSpecial xlPasteValues

  Set objTable = Sheets("5Felicia for MFG").ListObjects.Add(xlSrcRange, dstRng, , xlYes)
  objTable.TableStyle = "TableStyleMedium2"
End Sub

Upvotes: 1

Related Questions