Pivot table data source keeps referring to previously inserted worksheet's pivot table datasource

Question

I am generating two worksheets, one for data and one for pivot table named 'worksheet1_data' and 'worksheet1' respectively in same workbook using ep plus. It seems good when I save for first time. Again I process this excel file (.xlsx) and added two new worksheets 'worksheet2_data' and 'worksheet2' in it and generated new pivot table with datasource from 'worksheet2_data'. When I open the file, pivot table in 'worksheet1' and 'worksheet2' both have datasource of 'worksheet1_data', even if I add more pivot tables, all have datasource of 'worksheet1_data'.

The code works fine while debugging but datasources change while opening excel sheet. Can any one suggest what could be the issue?

Below is my code:

ExcelWorksheet worksheetManagementARReportData = package.Workbook.Worksheets.Add(EndDate.ToString("MM-dd-yyyy") + "_Data");
ExcelWorksheet worksheetManagementARReport = package.Workbook.Worksheets.Add(EndDate.ToString("MM-dd-yyyy"));
var dataRange_ManagementARReport = worksheetManagementARReportData.Cells["A1"].LoadFromDataTable(dsGetManagementARReport.Tables[0], true);
var pivotTableManagementARReport = worksheetManagementARReport.PivotTables.Add(worksheetManagementARReport.Cells["A1"], dataRange_ManagementARReport, "PivotTable" + EndDate.ToString("MM_dd_yyyy") + "_ManagementARReport");
Tbl_AutomatedReport.PivotTable(rowfield, columnfield, pagefield, datafields, pivotTableManagementARReport);

Answer 1

Can you just update the pivot range that is being used in each of the workbooks? Something like this:

Private Sub UpdatePivotRange()

Dim sName

lRow = Sheet1.Range("A2").End(xlDown).Row
Lcol = Sheet1.Range("A2").End(xlToRight).Column

    sName = Sheet1.Name
    pivRange = Sheet1.Name & "!R2C1:R" & lRow & "C" & Lcol


End Sub

Answer 2

Updating pivot range of each worksheet every time i open file as below code works:

  Private Sub Workbook_Open()
    'Set Variables Equal to Data Sheet and Pivot Sheet
    Dim Data_sht As Worksheet
    Dim Pivot_sht As Worksheet
    Dim StartPoint As Range
    Dim DataRange As Range
    Dim PivotName As String
    Dim NewRange As String
    Dim WS_Count As Integer
    Dim I As Integer
    WS_Count = ActiveWorkbook.Worksheets.Count
    ' Begin the loop.
    For I = 1 To WS_Count
    If ThisWorkbook.Worksheets(I).PivotTables.Count <> 0 Then
    Set Data_sht = ThisWorkbook.Worksheets(I - 1)
    Set Pivot_sht = ThisWorkbook.Worksheets(I)
    PivotName = "PivotTable" + Replace(Pivot_sht.Name, "-", "_") + "_ManagementARReport"
    Set StartPoint = Data_sht.Range("A1")
    Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
    NewRange = Data_sht.Name & "!" & _
    DataRange.Address(ReferenceStyle:=xlR1C1)
    'Change Pivot Table Data Source Range Address
    Pivot_sht.PivotTables(PivotName).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)
    'Ensure Pivot Table is Refreshed
    Pivot_sht.PivotTables(PivotName).RefreshTable
    End If
    Next I
    End Sub