How can I change my pivot table record source?

[penndro]

Hi I have some code that filters and cuts my master file into individual files. The code accomplishes this fine; however, in the NEW files generated - it references the master data file and does not have the filtered data (which is present in the target file).

Can someone lend some assistance to add this "Pivottablewizard" function in this code? I need each file to update the data source to the local file. and refresh the pivots.

Thanks,
Rick


<code>
Sub CutReport()
' This macro takes values in the range chsrList
' and breaks it in to multiple lists
' and saves them to separate files.

'=================================
' Variable Initialization
'=================================
Dim Wb As Workbook
Dim Wb_New As Workbook
Dim Ws_MasterTable As Worksheet
Dim Ws_Tab1 As Worksheet
Dim Ws_Tab2 As Worksheet
Dim Str As Variant
Dim RowNumber As Integer
Dim CheckPoint As Variant
Dim curPath As String
Dim cell As Range

'======================================
' Set Wb and worksheet objects
'======================================
Set Wb = ThisWorkbook
Set Ws_MasterTable = Wb.Worksheets("Master Table")
Set Ws_Tab1 = Wb.Worksheets("SBO-to-CBO Response Rates")
Set Ws_Tab2 = Wb.Worksheets("CBO-to-SBO Response Rates")

'======================================
' Set Current excel workbook path
'======================================
curPath = ActiveWorkbook.Path & "\"

Application.ScreenUpdating = False
Application.DisplayAlerts = False


For Each cell In Range("lstOffice")

'======================================
' Create New workbook
'======================================
Set Wb_New = Workbooks.Add

'======================================
' Send Tab1 data into Sheet1
'======================================
Ws_Tab1.Cells.Copy
Wb_New.Sheets("Sheet1").Paste
Wb_New.Sheets("Sheet1").Name = "SBO-to-CBO Response Rates"

'======================================
' Send Tab2 data into Sheet1
'======================================
Ws_Tab2.Cells.Copy
Wb_New.Sheets("Sheet2").Paste
Wb_New.Sheets("Sheet2").Name = "CBO-to-SBO Response Rates"

Ws_MasterTable.Activate
[ValOffice] = cell.Value
Range("chsrList").AdvancedFilter Action:=xlFilterCopy, _
criteriarange:=Range("Criteria"), copyToRange:=Range("Extract"), unique:=False
Range(Range("Extract"), Range("Extract").End(xlDown)).Copy

Wb_New.Sheets("Sheet3").Paste
Wb_New.Sheets("Sheet3").Name = "Master Table"


Wb_New.SaveAs Filename:=curPath & cell.Value & "_" & "Updated Chaser (Prod).xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Wb_New.Sheets("Master Table").Visible = xlSheetHidden
Wb_New.Close

Range(Range("Extract"), Range("Extract").End(xlDown)).ClearContents



Next cell


Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox "Done"

End Sub

<code/>

 

[SkipVought]

hi,

Turn on your macro recorder and record changing the PT Data Source reference.

PivotTable Tools Options > Data > Change Data Source

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}