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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
693
Office Version
  1. 2016
Platform
  1. MacOS
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
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
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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:

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

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,847
Members
452,361
Latest member
d3ad3y3

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
Back
Top