Copy Pivot Table Data to Another Worksheet Without Pivot in Excel: 4 Useful Methods

Method 1 – Use the Paste Special Feature to Copy Pivot Table Data Without Pivot

Select your whole pivot table and press Ctrl + C to copy it.

Use Paste Special Feature to Copy Pivot Table Data Without Pivot

Go to the new worksheet and select a cell where you want to copy it.
Right-click on it and click on Paste Special.

You will see the Paste Special dialogue box.
Select Values in the Paste category and press OK.

You will see only the values fetched from the pivot table.

Select the cell range B4:D8 and right-click again.
Select Formats in the Paste section.

Press OK, and you will get the pivot table without pivot in another worksheet.

If you need to match the column width with the original one, select Column widths in the Paste Special window after completing the above steps.

Method 2 – Apply the Clipboard Tool to Copy Data Without Pivot in Excel

Select the whole pivot table.

Go to the new worksheet and select the cell where you want to get the input.
Click on the Clipboard icon under the Home tab.

You will see that the Clipboard panel appears on the left.
Select the Item for pasting from the list.

You will get the pivot table data without pivot.

Method 3 – Excel VBA to Copy Pivot Table Data Without Pivot

Select any cell in the pivot table.
Go to the Developer tab and select Visual Basic under the Code group.

In the Visual Basic window, select Module from the Insert tab.

Insert this code in the blank page.

Sub PivotTableCopy()
Dim wst As Worksheet
Dim pivot As PivotTable
Dim rgPT As Range
Dim rgPTa As Range
Dim rgCopy As Range
Dim rgCopy2 As Range
Dim lRwTop As Long
Dim lRwsPT As Long
Dim lRwPage As Long
Dim msgSpc As String

On Error Resume Next
Set pivot = ActiveCell.PivotTable
Set rgPTa = pivot.PageRange
On Error GoTo errorHandler

If pivot Is Nothing Then
    MsgBox "Unable to copy"
    GoTo extHandler
End If

If pivot.PageFieldOrder = xlOverThenDown Then
  If pivot.PageFields.Count > 1 Then
    msgSpc = "Horizontal filters with spaces or blank cells." _
      & vbCrLf _
      & "Unable to copy Filters formatting."
  End If
End If

Set rgPT = pivot.TableRange1
lRwTop = rgPT.Rows(1).Row
lRwsPT = rgPT.Rows.Count
Set wst = Worksheets.Add
Set rgCopy = rgPT.Resize(lRwsPT - 2)
Set rgCopy2 = rgPT.Rows(lRwsPT)

rgCopy.Copy Destination:=wst.Cells(lRwTop, 2)
rgCopy2.Copy _
  Destination:=wst.Cells(lRwTop + lRwsPT - 2, 2)

If Not rgPTa Is Nothing Then
    lRwPage = rgPTa.Rows(1).Row
    rgPTa.Copy Destination:=wst.Cells(lRwPage, 1)
End If
    
wst.Columns.AutoFit
If msgSpc <> "" Then
  MsgBox msgSpc
End If

extHandler:
    Exit Sub
errorHandler:
    MsgBox "Unable to copy"
    Resume extHandler
End Sub

Click on the Run Sub button or press F5 on your keyboard.

You will successfully copy the pivot table data to another worksheet.

Method 4 – Copy Pivot Data with Report Filters to Another Worksheet

Drag the City title to the Filter category to specify it as a Report Filter.

You will get the Report Filter along with the pivot table like this.

Select the cell range B4:C4 that has the Report Filter and press Ctrl + C to copy it.
Go to the new worksheet and select a cell.
Press Ctrl + V to place the copy.

Copy the pivot table by pressing Ctrl + C again.
Press Ctrl + V to paste it into the new worksheet.

Things to Remember

In the case of the Report Filter, do not select any blank cells while copying.
If multiple Report Filters are arranged horizontally, you cannot copy the pivot table data with the VBA code. You will see an error message regarding this issue.

Download the Practice Workbook

Copy Pivot Table Data.xlsm