How to open an Excel workbook AND create a pivot chart using VBA Macro?

Question

I am trying to create a VBA Macro that opens an Excel file from a folder and then, in that file, creates a Pivot table. So far, I have been able to create two separate Macros that does each one of these operations separately i.e., one Macro that opens my desired Excel workbook and another Macro that creates the Pivot table for me.

I am looking to write ONE macro that does both of the above operations. Can anyone help me out please?

I am trying the below VBA code but it's giving me a "Run-time error '1004': Application-defined or object-defined error"


Sub Call_a_Macro()

    'Path of the file that has the macro
    Workbooks.Open ("C:\Users\User\File_ABC.xlsm")
    
    'Create the Pivot Table
    ActiveWorkbook.Worksheets("Sheet1").Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = _
        "Table2"
    Range("Table2[#All]").Select
    ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table2").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table2").Sort.SortFields. _
        Add2 Key:=Range("Table2[[#All],[Location]]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table2").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "BC"
    Range("F9").Select
    ActiveCell.FormulaR1C1 = "ON"
    Range("F10").Select
    ActiveCell.FormulaR1C1 = "SK"
    Range("F11").Select
    ActiveCell.FormulaR1C1 = "MK"
    Range("F12").Select
    ActiveCell.FormulaR1C1 = "SS"
    Range("F13").Select
    ActiveCell.FormulaR1C1 = "RR"
    Range("F14").Select
    ActiveCell.FormulaR1C1 = "AC"
    Range("F15").Select
    ActiveCell.FormulaR1C1 = "IO"
    Range("G8").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Table2[[#All],[Location]],""*BC*"")"
    Range("G9").Select
    ActiveWindow.SmallScroll Down:=-6
    ActiveCell.FormulaR1C1 = "=COUNTIF(Table2[[#All],[Location]],""*ON*"")"
    Range("G10").Select
    ActiveWindow.SmallScroll Down:=-8
    ActiveCell.FormulaR1C1 = "=COUNTIF(Table2[[#All],[Location]],""SK*"")"
    Range("G11").Select
    ActiveWindow.SmallScroll Down:=-9
    Range("G10").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Table2[[#All],[Location]],""*MK*"")"
    Range("G11").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Table2[[#All],[Location]],""*SS*"")"
    Range("G12").Select
    ActiveWindow.SmallScroll Down:=-10
    ActiveCell.FormulaR1C1 = "=COUNTIF(Table2[[#All],[Location]],""*RR*"")"
    Range("G13").Select
    ActiveWindow.SmallScroll Down:=-11
    ActiveCell.FormulaR1C1 = _
        "=COUNTIF(Table2[[#All],[Location]],""*AC*"")"
    Range("G14").Select
    ActiveWindow.SmallScroll Down:=-12
    ActiveCell.FormulaR1C1 = "=COUNTIF(Table2[[#All],[Program]],""AC*"")"
    Range("G15").Select
    ActiveWindow.SmallScroll Down:=-13
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=COUNTIF(Table2[[#All],[Location]],""*DE*"")-R[-1]C"
    Range("G16").Select

End Sub

your code is unnecessarily complex ... there is no reason to `select` a cell to edit its content ... use `Range("F8").FormulaR1C1 = "BC"` instead of `Range("F8").Select` `ActiveCell.FormulaR1C1 = "BC"` — jsotola, Jan 13 '23 at 18:02
So it opens my desired workbook and selects the table but doesn't create the pivot table as I would want. When I do f8 it shows the error comes at line: Range("Table2[#All]").Select — Konain Qurban, Jan 13 '23 at 18:10
that information belongs in the question ... please add it there ... then delete the comment ... this site is not a forum, it is a question and answer site ... all information needs to be contained in the question — jsotola, Jan 13 '23 at 18:18
You would benefit from checking [How to Avoid Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) out. — Cameron Critchlow, Jan 13 '23 at 18:20
There is something odd with your code, you set G10 to `"=COUNTIF(Table2[[#All],[Location]],""SK*"")"` and then overwrite it with `"=COUNTIF(Table2[[#All],[Location]],""*MK*"")"` ? Is `"=COUNTIF(Table2[[#All],[Program]],""AC*"")"` correct, all the others are `Location`. Also `"=COUNTIF(Table2[[#All],[Location]],""*ON*"")"` will count the header as it contains `ON` in the word Location. — CDP1802, Jan 14 '23 at 11:00

How to open an Excel workbook AND create a pivot chart using VBA Macro?

0 Answers0