Copy Pivot Table Data to Another Worksheet Without Pivot in Excel

In Excel, Pivot Table is one of the most powerful tools, especially for large datasets. It helps to summarize, organize and filter values from the huge data string. But sometimes after creating a pivot table, we need to copy it to another worksheet but not as a pivot. The process only keeps the values and formatting of the original one. There are several ways to copy pivot table data without a pivot in Excel. In this article, we will describe 4 useful methods to do it.


Download Practice Workbook

Get this sample file to try the methods by yourself.


What Is Pivot Table in Excel?

You may have thousands of data in your worksheet which makes it really confusing to understand. This is where we need a Pivot Table. A pivot table is a tool in Microsoft Excel that helps to explore this huge dataset in a summarized, precise pattern. It helps to filter according to groups, labels or categories for better understanding. Even you can expand or collapse and rotate rows and columns to get data in various dimensions. It also gives you cumulative totals for certain values.


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

To copy a pivot table, we need to create one first. For this, follow the quick steps below.

  • In the beginning, we created a dataset where 4 cities’ have various amounts of Sales and Profit on different Dates.

Copy Pivot Table Data to Another Worksheet Without Pivot

  • Then, select this whole dataset and go to the Insert tab.
  • Here, select From Table/Range under the PivotTable section.

  • Following, select the Location where you want to get the pivot table and hit OK.

  • Afterward, place your required fields in their respective sections according to your preference like this.

  • That’s it, we have our desired pivot table beside our original dataset.


Now, let us follow the methods below to copy this pivot table to another worksheet without pivot features.

1. Use Paste Special Feature to Copy Pivot Table Data Without Pivot

In this first method, we will apply the Paste Special feature of excel to copy pivot table data. To do the task, follow the steps below.

  • First, select your whole pivot table and press Ctrl + C to copy it.

Use Paste Special Feature to Copy Pivot Table Data Without Pivot

  • Then, go to the new worksheet and select a cell where you want to copy it.
  • Afterward, right-click on it and click on Paste Special.

  • Next, you will see the Paste Special dialogue box.
  • Here, select Values in the Paste category and press OK.

Use Paste Special Feature to Copy Pivot Table Data Without Pivot

  • Following, you will see that there are only the values fetched from the pivot table.

  • Now, select the cell range B4:D8 and right-click again.
  • Then, select Formats in the Paste section.

Use Paste Special Feature to Copy Pivot Table Data Without Pivot

  • Lastly, press OK and you will get the pivot table without pivot in another worksheet.

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


2. Apply Clipboard Tool to Copy Data Without Pivot in Excel

Another quick way is to use the Clipboard tool in Excel for copying pivot table data. Among all, this is the fastest method for the process.

  • In the beginning, select the whole pivot table that we created earlier.

Apply Clipboard Tool to Copy Data Without Pivot in Excel

  • Then, go to the new worksheet and select the cell where you want to get the input.
  • Following, click on the Clipboard icon under the Home tab.

Apply Clipboard Tool to Copy Data Without Pivot in Excel

  • After this, you will see that the Clipboard panel appears on the left.
  • Here, select the Item for pasting from the list.

  • Finally, you will get the pivot table data without pivot like this.


3. Excel VBA to Copy Pivot Table Data Without Pivot

Using Excel VBA Macro code is another useful technique for copying pivot table data to another worksheet. To conduct the task, go through the steps below.

  • First, select any cell in the pivot table.
  • Then, go to the Developer tab and select Visual Basic under the Code group.

Excel VBA to Copy Pivot Table Data Without Pivot

  • Next, in the Visual Basic window, select Module from the Insert tab.

  • After this, 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

Excel VBA to Copy Pivot Table Data Without Pivot

  • Following, click on the Run Sub button or press F5 on your keyboard.

  • Finally, you will successfully copy the pivot table data to another worksheet.


4. Copy Pivot Data with Report Filters to Another Worksheet

In this last method, we will copy pivot table data that has report filters with it. Report filters are the signified categories that we get in the PivotTable Fields. Now, follow the process below to copy data to another worksheet without pivot.

  • In the beginning, drag the City title to the Filter category to specify it as a Report Filter.

Copy Pivot Data with Report Filters to Another Worksheet

  • Afterward, you will get the Report Filter along with the pivot table like this.

  • Now, select the cell range B4:C4 that has the Report Filter and press Ctrl + C to copy it.
  • Then, go to the new worksheet and select a cell.
  • Following, press Ctrl + V to place the copy.

  • Similarly, copy the pivot table by pressing Ctrl + C again.
  • Finally, 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 during running the VBA code. Otherwise, you will see an error message regarding this issue.
  • Following any of the methods above benefits you with small file size, but may lose some sensitive data of the original pivot table.

Conclusion

Concluding the article, hoping that it was helpful for you. Go through the tutorial carefully and copy pivot table data to another worksheet without the pivot with 4 useful methods. Let us know your insightful suggestions in the comment box. Follow ExcelDemy for more articles.

Sanjida Mehrun Guria

Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo