Pivot table source changing

Question

I've created a macro to copy multiple sheets form a workbook and save it as a new workbook after changing the used value of all the sheets as value pasting. However i have 2 sheets with pivot tables in the sheets, and those pivot table source data is showing the previous workbook instead of new workbook. can someone help me to change the source to active workbook. I'm copying the source data also to the new workbook. here the source data sheets is "Detailed EPM" my source data is not dynamic. it is fixed always.(range "A4:AF76")

Sub Export_Final()
Dim Wb As Workbook
Dim NewWb As Workbook


Set Wb = ActiveWorkbook

Wb.Sheets(Array("Team Utilisation", "Task wise Utilisation", "Detailed EPM", 
"Pivot Production", "Pivot Non Production")).Copy
Dim rs As Worksheet

For Each rs In ActiveWorkbook.Worksheets
If rs.Name <> "Pivot Production" And rs.Name <> "Pivot Non Production" Then
rs.UsedRange = rs.UsedRange.Value
End If
Next rs

Dim FileSaveName As String
Dim fPath As String
fPath = ThisWorkbook.Path & "\"
tDate = VBA.Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd-mm-
yyyy")

Set NewWb = ActiveWorkbook
NewWb.SaveAs fPath & "Updated " & tDate & ".xlsm", FileFormat:=52
NewWb.Close

End Sub

Can someone help?

jeffreyweir jeffreyweir · Accepted Answer · 2018-02-09T06:02:36

I'd suggest instead of copying sheets to a new workbook, it will be easier to save a new copy the whole workbook and then delete the sheets you don't want, leaving just the ones you do. That way you don't have to repoint the PivotTable to the new data source. If you do it the way you currently are, all the PivotFields from the PivotTable will be cleared, which is an additional pain.

Pivot table source changing

1 Answers