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
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. ThisWorkbook.RefreshAll
    3. 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
    1. Sub CreateTable()
    2. Dim src As Range
    3. Dim ws As Worksheet
    4. Set src = Range("A1").CurrentRegion
    5. Set ws = ActiveSheet
    6. ws.ListObjects.Add(SourceType:=xlSrcRange, Source:=src, _
    7. xlListObjectHasHeaders:=xlYes, tablestyleName:="TableStyleMedium28").Name = "DataBase"
    8. End Sub

    If I've been helpful, let me know. If I haven't, let me know that too.