Hi all.
I have a series of daily reports that I run for a client. Based upon multiple sources of external data, I use a PivotTable to combine and perform calculations.
The PivotTable reports have been formatted, complete with page headers and footers. I need a way to paste the values (and NOT the PivotTable) to a new workbook so that all formatting--including headers, footers, etc--is copied as well.
Additionally, there are filters applied to the PivotTables. So I need to copy only those values displayed.
I have been playing around with VBA, but have had no success. Here's my code:
-----
Sub PasteSheetsValues()
Dim w As Worksheet
ActiveWindow.SelectedSheets.Copy
For Each w In ActiveWorkbook.Sheets
With w.UsedRange
.Value = .Value
End With
Next w
End Sub
-----
Unfortunately, this macro does not work when a worksheet contains a PivotTable. I receive a Run-time error 1004 ("You cannot move a part of a PivotTable report...).
I'd welcome any suggestions.
Thanks!
I have a series of daily reports that I run for a client. Based upon multiple sources of external data, I use a PivotTable to combine and perform calculations.
The PivotTable reports have been formatted, complete with page headers and footers. I need a way to paste the values (and NOT the PivotTable) to a new workbook so that all formatting--including headers, footers, etc--is copied as well.
Additionally, there are filters applied to the PivotTables. So I need to copy only those values displayed.
I have been playing around with VBA, but have had no success. Here's my code:
-----
Sub PasteSheetsValues()
Dim w As Worksheet
ActiveWindow.SelectedSheets.Copy
For Each w In ActiveWorkbook.Sheets
With w.UsedRange
.Value = .Value
End With
Next w
End Sub
-----
Unfortunately, this macro does not work when a worksheet contains a PivotTable. I receive a Run-time error 1004 ("You cannot move a part of a PivotTable report...).
I'd welcome any suggestions.
Thanks!
