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 · Accepted Answer · 2018-02-09 06:02:36Z

1

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.

answered Feb 9, 2018 at 6:02

jeffreyweir

4,8841 gold badge19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

aji Over a year ago

Thanks a lot for the suggestion. I've tried it but still the source data for the pivot is redirecting to the primary excel file. The code which i tried is Thisworkbook.sheets.copy No idea what to do at this point

aji Over a year ago

Hi Slight change on my comment, I've changed the copy sheets in the workbook option as save as current workbook. now it worked perfect

Collectives™ on Stack Overflow

Pivot table source changing

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related