ndbass_2001
12-06-2013, 02:38 PM
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!
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!