"Invalid procedure call or argument" when using a Macro

[Mattnet]

Hello everyone on MrExcel,
I thank in advance anyone that will help me resolve my problem. At work my collegue uses the below Macro to extract data from an Excel generated by a program (HP Quality Center, in postprocessing). For some reason, the Macro works for him but not for me. He uses Excel 2007 and I use Excel 2013.

When running the Macro, Visual Basic gives the following error "Invalid procedure call or argument". I have tried a multitude of fixes I found online like taking in/out ' and ", emptying the Tabledestination field, changing the pivot table version and many other things. I am not proficient with VBA at all so I don't know where else to put my hands beside where the debugger tells me to.

Does anyone know how I can fix this? I cannot thank enough the person that can fix this as I currently need to do everything manually and it takes forever. I have two more scripts for two other macros but the code is very similar so I think I'll be able to fix them myself once we find a solution for this one.

Thank you all!

Sub QC_PostProcessing()Dim MainWorksheet As Worksheet
' Make sure your worksheet name matches!
Set MainWorksheet = ActiveWorkbook.Worksheets("Query1")
Dim DataRange As Range
Set DataRange = MainWorksheet.UsedRange
' Now that you have the data in DataRange you can process it.


' Table Title
    Range("A1:T1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True


' Autofit
    Columns("A:E").EntireColumn.AutoFit
    Columns("J:T").EntireColumn.AutoFit


' Table grid
    Range("A1:T1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With


' Width and alignment
    Columns("F:I").Select
    Selection.ColumnWidth = 40
    Selection.NumberFormat = "General"


    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With


    Range("A1").Select


' Pivot
    Dim Data_sht As Worksheet
    Dim StartPoint As Range
    Dim DataRange2 As Range
    Dim NewRange As String
    Set Data_sht = ThisWorkbook.Worksheets("Query1")
    Set StartPoint = Data_sht.Range("A1")
    Set DataRange2 = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
    NewRange = Data_sht.Name & "!" & _
    DataRange2.Address(ReferenceStyle:=xlR1C1)
    'Query1!R1C1:R5000C7"


    Selection.Copy
    Application.CutCopyMode = False


[COLOR=#ff0000]    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _[/COLOR]
[COLOR=#ff0000]        NewRange, Version:=xlPivotTableVersionCurrent).CreatePivotTable _[/COLOR]
[COLOR=#ff0000]        TableDestination:="", TableName:="Tabella_pivot1", _[/COLOR]
[COLOR=#ff0000]        DefaultVersion:=xlPivotTableVersionCurrent[/COLOR]


    With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Release")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Status")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("Tabella_pivot1").AddDataField ActiveSheet.PivotTables( _
        "Tabella_pivot1").PivotFields("Severity"), "Count of Severity", xlCount
    With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Severity")
        .Orientation = xlColumnField
        .Position = 1
    End With


' Graph
    Range("K3").Select
    ActiveSheet.PivotTables("Tabella_pivot1").PivotSelect "", xlDataAndLabel, True
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Range("Sheet4!$A$3:$E$22")
    ActiveSheet.Shapes("Chart 1").IncrementLeft 200
    ActiveSheet.Shapes("Chart 1").IncrementTop -230.00
    ActiveSheet.Shapes("Chart 1").ScaleWidth 1.3270833333, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 1").ScaleHeight 1.3767362934, msoFalse, _
        msoScaleFromTopLeft


' Rename sheets
    Sheets("Sheet4").Select
    Sheets("Sheet4").Name = "Stats"
    Sheets("Query1").Select
    Sheets("Query1").Name = "Report"
    Sheets("Stats").Select
    Sheets("Stats").Move After:=Sheets(2)
    Range("A1").Select
    Sheets("Report").Select
    Range("A2").Select


End Sub

 

[Mattnet]

Anyone that can help?

 

[Mattnet]

No one can figure out the problem?