How can I copy and paste an entire sheet with pivot table and formats?

[ndbass_2001]

Hi All,

I'm working on a project in Excel 2010 where I'd like to basically take a snapshot of a Pivot Table and paste its values and formatting into a new worksheet using a Macro. This way when I send it out the end user can parse the data and then send only the relevant data to whomever needs it. I'm using slicers instead of the standard filters. The challenging portion is that I have formulas outside of the Pivot Table itself that also need to be moved and copied as values with formatting. I can get the Pivot table itself copied as values with formatting with this code:

Sub PivotCopyFormatValues()
Dim ws As Worksheet
Dim pt As PivotTable
Dim rngPT As Range
Dim rngPTa As Range
Dim rngCopy As Range
Dim rngCopy2 As Range
Dim lRowTop As Long
Dim lRowsPT As Long
Dim lRowPage As Long
On Error Resume Next
Set pt = ActiveCell.PivotTable
Set rngPTa = pt.PageRange
On Error GoTo errHandler
If pt Is Nothing Then
    MsgBox "Could not copy pivot table for active cell"
    GoTo exitHandler
Else
    Set rngPT = pt.TableRange1
    lRowTop = rngPT.Rows(1).Row
    lRowsPT = rngPT.Rows.Count
    Set ws = Worksheets.Add
    Set rngCopy = rngPT.Resize(lRowsPT - 1)
    Set rngCopy2 = rngPT.Rows(lRowsPT)
    
    rngCopy.Copy Destination:=ws.Cells(lRowTop, 1)
    rngCopy2.Copy Destination:=ws.Cells(lRowTop + lRowsPT - 1, 1)
End If
If Not rngPTa Is Nothing Then
    lRowPage = rngPTa.Rows(1).Row
    rngPTa.Copy Destination:=ws.Cells(lRowPage, 1)
End If
    
ws.Columns.AutoFit
exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not copy pivot table for active cell"
    Resume exitHandler
End Sub

I have not been able to figure out how I can copy the entire sheet and do the same so it includes the Pivot and all of the exterior formulas and information.

Any help or suggestions would be appreciated.


Thanks!

[snb]

can you please add code tags ?


you can copy a sheet using

Sub M_snb()
  activesheet.copy ,thisworkbook.sheets(thisworkbook.sheets.count)
End Sub

[SamT]

Rez Bump

Code tags added.