How to fix it when get "Invalid procedure call or argument"?

[feipezi]

Hi,

As I select a value from a dropdown, I get the error like in the subject. As I debug it, it takes me to a Sub like this (sentence of "ActiveSheet.PivotTables(1).PageFields("State").CurrentPage = Cells(1, "cw").Value" will be in yellow). Attached is a couple of procedures the Private Sub State_Change() calls.

The idea is to use PT to summarize and calculate the data then copy the PT datarange to another location to update a chart on it.

Please let me know if more background info needed.

Thanks in advance.


Private Sub State_Change()
Application.ScreenUpdating = False
ActiveSheet.PivotTables(1).PageFields("State").CurrentPage = Cells(1, "cw").Value
Call SamePage
Call PTCopy
Cells(1, "bt") = ""
Cells(1, "bt").Value = ""
Application.ScreenUpdating = True
End Sub

Sub SamePage()
On Error GoTo Escape
cp_ou = Sheets("Account Trend").PivotTables(1).PageFields("ou").CurrentPage
cp_state = Sheets("Account Trend").PivotTables(1).PageFields("state").CurrentPage
cp_region = Sheets("Account Trend").PivotTables(1).PageFields("region").CurrentPage
cp_dist = Sheets("Account Trend").PivotTables(1).PageFields("district").CurrentPage
cp_pod = Sheets("Account Trend").PivotTables(1).PageFields("pod").CurrentPage
cp_acct = Sheets("Account Trend").PivotTables(1).PageFields("acct").CurrentPage
cp_flag = Sheets("Account Trend").PivotTables(1).PageFields("tgt_flag").CurrentPage
Sheets("TKTShare").PivotTables(1).PageFields("ou").CurrentPage = cp_ou
Sheets("TKTShare").PivotTables(1).PageFields("state").CurrentPage = cp_state
Sheets("TKTShare").PivotTables(1).PageFields("region").CurrentPage = cp_region
Sheets("TKTShare").PivotTables(1).PageFields("district").CurrentPage = cp_dist
Sheets("TKTShare").PivotTables(1).PageFields("pod").CurrentPage = cp_pod
Sheets("TKTShare").PivotTables(1).PageFields("acct").CurrentPage = cp_acct
Sheets("TKTShare").PivotTables(1).PageFields("tgt_flag").CurrentPage = cp_flag
Escape: Exit Sub
End Sub

Sub PTCopy()
Dim pt1 As PivotTable
Dim pt2 As PivotTable
Set pt1 = Sheets("Account Trend").PivotTables(1)
Set pt2 = Sheets("TKTShare").PivotTables(1)
Dim curregn As Range
Dim tktrng As Range
Dim newrng As Range
totrows = pt1.TableRange1.Rows.Count
totcols = pt1.ColumnRange.Columns.Count
On Error Resume Next
Range("mainrng").Delete
Range("minrrng").Delete
On Error GoTo 0
pt1.TableRange1.Offset(1).Resize(totrows - 1, totcols + 1).Name = "mainrng"
With pt2
If .DataBodyRange.Offset(-1).Resize(1, 1).Value <> "AHY" Then
.DataBodyRange.Offset(-1).Resize(pt2.DataBodyRange.Rows.Count + 1, 1).Name = "minrrng"
Else: Range("az1:az12").Name = "minrrng"
End If
End With
Sheets("Account Trend").Activate
Cells(10, 1).CurrentRegion.Clear
Range("mainrng").Copy Cells(10, 1)
lastc = Cells(10, 1).CurrentRegion.Columns.Count
Range("minrrng").Copy Cells(10, lastc)
End Sub

 

[SkipVought]

Hi,

I would replace all activesheet references with an explicit sheet reference, unless you can say, with certainty that 1) the procedure will only be executed when a sheet with the appropriate object, like pivot table, will be present and ALL other references will be correct.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Skip,

You're the Man again. It worked.

Thanks a lot.