ExcelInsider Team
Pivot tables are used for a lot of data analysis purposes in Excel. Sometimes, we need to copy the pivot table to another sheet so that we can have different layouts or formats of the same data in other sheets. In this article, we will learn how to copy a pivot table to another sheet.
➤ Select all of the cells of the pivot table.
➤ Right click and select Copy.
➤ Paste the cells in another sheet by pressing Enter.
That was the regular copy-paste method. However, there are other methods of copying a pivot table to another sheet as well. In this article, we will show you all the techniques you can use to copy a pivot table to another sheet.
Copying Using the Clipboard
In the datasheet, we have budgetary information for a few months. There are categories, months, and whether the budgets are under, on track, or over budget based on the actual budgets and the money spent.
➤ Select the cells on your pivot table.
➤ Right-click to open the context menu. From there, select Copy. You can also press Ctrl + C on the keyboard.
➤ The table will be copied to the clipboard.
➤ Go to your new sheet and select the cell where you want to paste the table.
➤ In the new cell, press Enter or Ctrl + V .
➤ If you use Enter, Excel will no longer consider the pivot table as a copied item. If you press Ctrl + V , you will need to press Esc again to disable the “copied” state.
Copy by Dragging
Instead of using the clipboard, you can just drag the pivot table into a new sheet to copy it. Here are the steps on how to do that:
➤ Select the table like you did before
➤ Go to the edge of the table until you find an icon in your mouse cursor like the image below:
➤ Click using your mouse and hold Alt + Ctrl . Now, drag the table to your new sheet.
➤ Leave the table at the location where you want to copy.
Duplicating the Sheet
If you want to copy the pivot table to an entirely new sheet, you can duplicate the sheet. Here is how to do that:
Option 1: Using the “Move or Copy” Dialog
➤ Right-click on the sheet, and select Move or Copy
➤ From the new dialog, check the box that says, “Create a copy”. If you want to copy to a whole new Excel book, you can select (new book) from the To book: Otherwise, click OK to copy. The copy will be created at the beginning. You can select any other position from that dialog as well.
Option 2: Drag and Copy
➤ Select the pivot sheet you want to copy.
➤ Hold Ctrl and drag the sheet next to any sheet.
➤ Now the sheet will be copied to the location you have dragged it to.
Using VBA Code
If regular copy and paste is not enough for you, you can go for some advanced procedures and use VBA code to copy the pivot table to another sheet. Here is how to do that.
➤ Press Alt + F11 to open the VBA window.
➤ Go to Insert > Module from the top bar.
➤ Write this code in the code window:
Sub CopyPivotTable()
Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet
Dim pivotTable As pivotTable
Dim targetRange As Range
Set sourceSheet = ThisWorkbook.Sheets("PivotSheet")
Set destinationSheet = ThisWorkbook.Sheets("PivotSheetVBA")
Set pivotTable = sourceSheet.PivotTables(1)
Set targetRange = destinationSheet.Range("A1")
pivotTable.TableRange2.Copy
targetRange.PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
End Sub
➤ Replace PivotSheet with the sheet where your source pivot table exists, PivotSheetVBA with the new sheet where you want to copy, and A1 with the range where you want to copy the pivot table to.
➤ Press F5 to run the code.
➤ Go back to the spreadsheet to see the result.
Frequently Asked Questions
How to extract data from a PivotTable to another sheet?
Enter this formula in the other sheet where you want to see the data:
=PivotSheet!A3:C29
Here, PivotSheet is the name of the sheet where the pivot table exists, and A3:C29 is the range of the pivot table.
How to Copy PivotTable data to another sheet without pivot?
After copying the pivot table data as the tutorial in this article says. While pasting in another sheet, instead of selecting the regular paste icon, select the Paste Value to copy the data without the pivot.
How to Copy and paste a PivotTable without losing formatting?
After you copy the data without the table, select the source data again and click on the Format Painter icon on the Clipboard section in the Home tab. Select the destination cells and press the Format Painter icon again. The cell formats will be copied to the new cells.
How do I move a PivotTable?
Select a cell from the pivot table for the tabs related to the pivot table to show up. From the PivotTable Analyze tab, select Move PivotTable from the Actions section. Click OK from the new dialog after choosing whether you want to move it to a new worksheet or an existing one.
What is the shortcut formula for pivot?
After converting your data range to a table, press Alt > JT > V . A new dialog box will open to create your pivot table.
Wrapping Up
In this article, we have learned various methods on how to copy a pivot table to another sheet. We have included the workbook that we used in this tutorial for your convenience. Leave a comment and suggest what tutorial you want to see next. Consider bookmarking our site and visiting every day to learn something new.
