Pivot table data source

Question

I have one workbook that contains a large database.

(Lets call this Basefile.xlsm)

I have made code that sorts data according to department, and uses this to update some pre existing pivot tables.

The pivot tables and sorted data are then saved in a seperate file for each department.

(Lets call these Department1.xls, Department2.xls etc).

My problem is that each new file's pivot table still refers to the original workbook rather than the new ones.

(So the pivot tables in Department1.xls should get its data from a sheet in Department1.xls, but currently all the pivot tables still use Basefile.xlsm as a datasource)

Is there a way to correct this? - without making all the pivot tables with code?

    Sub Selectdata()


  Application.ScreenUpdating = False2



' filters for nivå2 enhet, cuts and pastes data into a sheet named after nivå2 -  ready for creating pivot table

Dim i As Integer
Dim WS As Worksheet

For i = Worksheets("Department").Range("g4").Value To Worksheets("Department").Range("h4").Value

Sheets("Basefile 2014").Select
ActiveSheet.Range("A:O").AutoFilter Field:=15, Criteria1:= _
    Worksheets("Department").Range("b" & i).Value

Cells.Select
Range("A29619").Activate
Selection.Copy
Sheets("Metode").Select
Set WS = Sheets.Add
ActiveSheet.Paste
WS.Name = "RawData 2014"

'shows all the data in the new worksheet
WS.Select
 ActiveSheet.Range("A:O").AutoFilter Field:=15
     Columns("l:l").Select

' repeats proceedure for 2013
Sheets("Basefile 2013").Select
ActiveSheet.Range("A:O").AutoFilter Field:=15, Criteria1:= _
Worksheets("Department").Range("b" & i).Value
Cells.Select
Range("A29619").Activate
Selection.Copy
Sheets("Metode").Select
Set WSD = Sheets.Add
ActiveSheet.Paste
WSD.Name = "RawData 2013"

'shows all the data in the new worksheet
WSD.Select
 ActiveSheet.Range("A:O").AutoFilter Field:=15
     Columns("l:l").Select

 'Refreshes all the pivot table data
 ActiveWorkbook.RefreshAll

 Application.ScreenUpdating = True

Filename = Worksheets("Department").Range("c" & i).Value & "2014"
Fname = Worksheets("Department").Range("c" & i).Value & "2013"


Sheets(Array("Funn 2013", "Pivot 2013", "RawData 2013")).Copy

With ActiveWorkbook
.SaveAs "F:\X Simulation\test\" & Fname
.Close
End With


Sheets(Array("Pivot 1.", "Pivot 2.", "Pivot 3.", "Pivot 4.", "Funn 2014", "RawData 2014")).Copy



With ActiveWorkbook
.SaveAs "F:\X Simulation\test\" & Filename
.Close
End With

Application.DisplayAlerts = False
Worksheets("RawData 2014").Delete
Worksheets("RawData 2013").Delete
Application.DisplayAlerts = True


 Next i

End Sub

MP24 MP24 · Accepted Answer · 2014-05-08T18:35:36

This is possible by changing the SourceData property of the pivot table.

Worksheets("PivotTable").PivotTables(1).SourceData = Worksheets("SourceData").Range("A1:Z999").Address

You will need to edit the worksheets parameters, of course, and the data range from where your source data comes.

You could also record the appropriate steps, i.e. editing the source of the pivot table in your Department1.xls sheet, with the Macro Recorder and see what code it produces.

Pivot table data source

1 Answers