Excel – Pivot Table not updating or not working properly

excelpivot tablevba

So I have a pivot table with a data source internal in the workbook just a different sheet but it's not updating/refreshing whenever i click refresh nothing happens. I really need to modify my data source with a different sheet or different range just to update the pivot. It's like pivottable refresh is not working even though i deleted the old data sheet and created new one it's still the same also even though i deleted the pivot table and create a new one it's also not refrehsing what's wrong with my excel file.

Best Answer

Not sure if this is the reason, but have you checked the Display Language (are you using the same excel sheet on the same machine)?

For instance I created a pivot table and a chart, using the Display Language = English, only to find out that if German was selected on another machine, the datasource wouldn't update anymore.

I cannot find any way to make the datasource language independent. E.g. If the filed is called Column1 (which would be the default name of a field in EN) well, in German that is... Spalte1. Which of course prevents the chart to be updated, even when reading the same datasource!

Renamed fields are not preserved when switching from a Display Language to another. So far the only way to make the graphical representation available is to create... two Excel files, one for German settings, the other for English.

Related Solutions

Excel – Auto Refresh Excel 2007 Pivot Table after data in separate sheet updated from SQL Server DB

You can use the worksheet changed event to trigger a refresh on your pivot table.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ErrHandler
    Application.EnableEvents = False

    'Check to see if the worksheet range raising this event overlaps the range occupied by our data dump
    If (Not (Intersect(Target, ActiveSheet.ListObjects("DATA_TABLE_NAME_HERE").Range) Is Nothing)) Then

        'If it does, then refesh the pivot tables that depend on this data (not automatic, name each table pivot table explicity)
        ActiveSheet.PivotTables("PIVOT_TABLE_NAME_HERE").RefreshTable

    End If

ErrHandler:
    Application.EnableEvents = True
End Sub

You might need to replace ActiveSheet with Sheets("whatever") depending on how your workbook frames up.

Python – Using Pandas to pd.read_excel() for multiple worksheets of the same workbook

Try pd.ExcelFile:

xls = pd.ExcelFile('path_to_file.xls')
df1 = pd.read_excel(xls, 'Sheet1')
df2 = pd.read_excel(xls, 'Sheet2')

As noted by @HaPsantran, the entire Excel file is read in during the ExcelFile() call (there doesn't appear to be a way around this). This merely saves you from having to read the same file in each time you want to access a new sheet.

Note that the sheet_name argument to pd.read_excel() can be the name of the sheet (as above), an integer specifying the sheet number (eg 0, 1, etc), a list of sheet names or indices, or None. If a list is provided, it returns a dictionary where the keys are the sheet names/indices and the values are the data frames. The default is to simply return the first sheet (ie, sheet_name=0).

If None is specified, all sheets are returned, as a {sheet_name:dataframe} dictionary.

Best Answer

Related Solutions

Excel – Auto Refresh Excel 2007 Pivot Table after data in separate sheet updated from SQL Server DB

Python – Using Pandas to pd.read_excel() for multiple worksheets of the same workbook

Related Topic