Need help regarding Invalid procedure call or argument

[Auri]

Hi, for some reason, when I ran my macro, this appears. The codes are shown below. Any help will be appreciated! Thanks!

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Sum of Amount"), "Sum of Amount", xlSum
    ActiveWindow.SmallScroll Down:=-3
    ActiveSheet.PivotTables("PivotTable2").PivotSelect _
        "'Count of Account Number (SHA)'", xlDataAndLabel, True
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = -6710887
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = -6710887
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = -6710887
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
End Sub

 

[Joe4]

If you hit the "Debug" button, what line of code does it highlight as being the offending line?

 

[Auri]

ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Sum of Amount"), "Sum of Amount", xlSum

This line

If you hit the "Debug" button, what line of code does it highlight as being the offending line?

 

[Joe4]

On that sheet, make sure that you Pivot Table is really named "PivotTable2" and has a field named "Sum of Amount".

 

[Auri]

It really has those so I was wondering why it have that occurring error.

 

[Michael M]

Make sure there are no extra spaces / characters in "Sum of Amount"
this

With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = -6710887
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = -6710887
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = -6710887
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

can also be reduced to

With Selection.Borders
        .LineStyle = xlContinuous
        .Color = -6710887
        .TintAndShade = 0
        .Weight = xlThin
    End With

 

[Auri]

Make sure there are no extra spaces / characters in "Sum of Amount"
this

With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = -6710887
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = -6710887
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = -6710887
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

can also be reduced to

With Selection.Borders
        .LineStyle = xlContinuous
        .Color = -6710887
        .TintAndShade = 0
        .Weight = xlThin
    End With

I made sure that there's no extra space but it still didn't work. And I applied the changes to shorten it. Thanks!

 

[Auri]

ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Number (SHA)"). _
        AutoSort xlDescending, "Sum of Amount"

Can I also know why this line doesn't work in macro? (What I meant was it doesn't apply the change)

 

[Auri]

Ok I managed to solve it. Thanks for the help!

 

[Joe4]

Ok I managed to solve it. Thanks for the help!

Can you share with us what the problem was?
That way others who encounter similiar problems know what to look for, and we may be able to help others who have similar problems better.