Create automatically pivot tables and pivot charts using VBA

  • I am developing a project in Excel, for this I developed a code that allows me to open multiple files, extract information from those documents and collect it in Sheet1, from this data on (Sheet1) I need to generate PivotTables and PivotCharts, so first I converted my data in Sheet1 into a table and manually create the PivotTables and Charts, after this I added a line of code to automatically updated my PivotTables when my (Sheet1) change, however pivot tables and therefore charts are not updating automatically when I run my first Macro in Sheet1.


    The following is the code I am using to convert my data into a table and the last lines are to automatically refresh the pivot tables.,


    If someone can help me I appreciate it, I am new in VBA and I dont know if this is a good way to develop the project


  • This bit of code is in the wrong worksheet code module.

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        ThisWorkbook.RefreshAll
    
    
    End Sub

    It should be in the worksheet module that has the tables and charts.


    And this code should be in a standard module, not a worksheet module.

    Code
    Sub CreateTable()
        Dim src As Range
        Dim ws As Worksheet
        Set src = Range("A1").CurrentRegion
        Set ws = ActiveSheet
        ws.ListObjects.Add(SourceType:=xlSrcRange, Source:=src, _
        xlListObjectHasHeaders:=xlYes, tablestyleName:="TableStyleMedium28").Name = "DataBase"
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!