Need help regarding Invalid procedure call or argument

Auri

Board Regular
Joined
Apr 8, 2020
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
Hi, for some reason, when I ran my macro, this appears. The codes are shown below. Any help will be appreciated! Thanks!
1587020227657.png

VBA Code:
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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,738
Office Version
  1. 365
Platform
  1. Windows
If you hit the "Debug" button, what line of code does it highlight as being the offending line?
 

Auri

Board Regular
Joined
Apr 8, 2020
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,738
Office Version
  1. 365
Platform
  1. Windows
On that sheet, make sure that you Pivot Table is really named "PivotTable2" and has a field named "Sum of Amount".
 

Auri

Board Regular
Joined
Apr 8, 2020
Messages
54
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,054
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Make sure there are no extra spaces / characters in "Sum of Amount"
this
VBA Code:
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
VBA Code:
With Selection.Borders
        .LineStyle = xlContinuous
        .Color = -6710887
        .TintAndShade = 0
        .Weight = xlThin
    End With
 

Auri

Board Regular
Joined
Apr 8, 2020
Messages
54
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Make sure there are no extra spaces / characters in "Sum of Amount"
this
VBA Code:
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
VBA Code:
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

Board Regular
Joined
Apr 8, 2020
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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

Board Regular
Joined
Apr 8, 2020
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
Ok I managed to solve it. Thanks for the help!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,738
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,092
Messages
5,628,620
Members
416,329
Latest member
phxdan79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top