Vba Help - Code works fine in Excel 2010 but errors out on one line in 2016???

[Johnny Thunder]

Anyone ever see these kinds of issues?

My code executes just fine and quickly in excel 2010 but errors out on one line in Excel 2016?

*Please forgive the archaic selects all over this code, I didn't write it but I have already re-written but the error still occurs on the same line.

Specific Error: Runtime Error 1004
Autofilter method of range class Failed

Code

Sub ByCostCenterCopyPast()
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    
    TimeStamp
    
    Sheets("Query Results").Visible = True
    Sheets("Query Results").Select
    Range("AR1:AR9").Select
    Selection.Copy
    Range("AS1:JG9").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Calculate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A12:AF12").Select
    Selection.Copy
    Range("A13:AF10000").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Calculate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("JS12:KA12").Select
    Selection.Copy
    Range("JS13:KA10000").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Calculate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("A13").Select
    Application.CutCopyMode = False


    Sheets("Query Results").Visible = False
    
    
    
    Sheets("PARAMETERS").Select
    
    Range("V57:Y58").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("By Cost Center").Select
    Range("A4:D5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh


    ActiveSheet.Range("$A$10:$CD$652").AutoFilter   '<------------------------------------------------------------------ Error line
    ActiveSheet.Range("$A$10:$CD$652").AutoFilter Field:=5
    Range("A12:E12").Select
    Selection.Copy
    Range("A13:E652").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("G12:CD12").Select
    Selection.Copy
    Range("G13:CD652").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Calculate
    Range("A13:E13").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G13:CD13").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("C2").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$10:$CD$652").AutoFilter Field:=5, Criteria1:="<>"
    
    Sheets("PARAMETERS").Select
    
    Range("AH59").Select
    Selection.Copy
    Sheets("Summary by Quarter").Select
    Range("B4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Sheets("PARAMETERS").Select
    Range("W57:X57").Select
    Selection.Copy
    Sheets("Summary").Select
    Range("B6:C6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
           
        
        
        


    Sheets("BPC Hierarchy by Cost Center").Select
    UpdateBPChierarchyReport
    
    Sheets("By Ter-Dep-BU-Cst-Com-Pft").Select
    By_Ter_BU_Dep_Flat_File
    
    Sheets("PARAMETERS").Select
    
    Range("v28").Select
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    

End Sub

 

[pvr928]

Hi @Johnny Thunder

Check what that line is referring to.

The previous line refers to 'ActiveSheet', which contains a PivotTable.

The line which errors also refers to 'ActiveSheet' - I'm putting it out there that Range("$A$10:$CD$652") intersects PivotTable3, or the range you want to AutoFilter (ie Range("$A$10:$CD$652")) is not on 'ActiveSheet'.

In other words, ensure your code is referring to the correct sheet. 'ActiveSheet'/'ActiveCell' are not robust ways to reference objects. Ensure you explicitly refer to the object you wish to manipulate by assigning it to a dimensioned object, and top it off by having 'Option Explicit' at the top of each Module.

In my experience with VBA there are little differences between editions such that any error debugging should start with fundamental issues in the code itself.

Cheers

pvr928

 

[Johnny Thunder]

Ok guys I found the issue but its a weird one s let me explain and maybe someone has a workaround or knows how to fix this.

Issue: In excel 2010, a user can have data from Column A:C and in Column D have a pivot table (one column of pivot data) and then in column E: whatever continue with regular data and throw a Autofilter across all of these header rows and it works.

the issue is that in Excel 2016 you can no longer have regular data mixed with a Pivot column and have it on the same Filter. I work for a corporate company where users have both Excel version and that is how I was able to identify this issue.

Hoping that someone has had this happen or knows of a way around this? I know it sounds stupid to have data mixed with pivot data but that is the problem I am faced with.

Hi @Johnny Thunder

Check what that line is referring to.

The previous line refers to 'ActiveSheet', which contains a PivotTable.

The line which errors also refers to 'ActiveSheet' - I'm putting it out there that Range("$A$10:$CD$652") intersects PivotTable3, or the range you want to AutoFilter (ie Range("$A$10:$CD$652")) is not on 'ActiveSheet'.

In other words, ensure your code is referring to the correct sheet. 'ActiveSheet'/'ActiveCell' are not robust ways to reference objects. Ensure you explicitly refer to the object you wish to manipulate by assigning it to a dimensioned object, and top it off by having 'Option Explicit' at the top of each Module.

In my experience with VBA there are little differences between editions such that any error debugging should start with fundamental issues in the code itself.

Cheers

pvr928

 

[pvr928]

Hi @Johnny Thunder

Would you please post your workbook as you describe in Excel 2010.

I have replicated your structure as you describe in Excel 2010 and the line:

ActiveSheet.Range("$A$10:$CD$652").AutoFilter

produces the error you say only occurs in Excel 2016.

To eliminate the error you are getting, as I say in my first post, you have to explicitly assign Objects to the Table (I assume it is an Excel Table) and the pivot table, and then manipulate those objects directly once assigned.

For example:

For the Table:

Sub Example()
Dim ws As Worksheet
Dim tbl As ListObject
 
Set ws = Sheets("PARAMETERS")
Set tbl = ws.ListObjects("Table1") '<- ADJUST FOR THE ACTUAL NAME OF THE EXCEL TABLE

tbl.Range.AutoFilter
End Sub

Filtering in a pivot table is more complex. As you have not provided information about the nature of the pivot table, including the pivot fields, no guidance can be provided, save for googling something like 'Excel VBA pivot table filter'.

Cheers

pvr928