VBA problems after upgrading to Excel 2016

[vandango05]

Hi.

I've got a worksheet with many macros that have worked fine in Excel 2010 that i've just stopped using buy many colleagues still are. Here is an example of some code that will no longer run.

Sub deleteimages()Dim s As String
Dim pic As Picture
Dim rng As Range


' Set ws = ActiveSheet
Set ws = ActiveWorkbook.Worksheets("Quotation")


Set rng = ws.Range("A26:L8000")


For Each pic In ActiveSheet.Pictures
With pic
s = .TopLeftCell.Address & ":" & .BottomRightCell.Address
End With
If Not Intersect(rng, ws.Range(s)) Is Nothing Then
pic.Delete
End If
Next


End Sub

It fails at the line

For Each pic In ActiveSheet.Pictures

another section of code i'm having problems with simply filters some data

ActiveSheet.Range("$K$5:$K$7475").AutoFilter Field:=1, Criteria1:=">0", _
        Operator:=xlAnd

It appears that 2016 doesn't much like the ActiveSheet part. No doubt i'm going to have lots more issues as more users adopt 2016 so any help would be appreciated.

 

[Yongle]

This line of code works perfectly with Excel 2016

ActiveSheet.Range("$K$5:$K$7475").AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd

There is probably something else going on that makes Excel think something else is active

ActiveSheet is not reliable if something else is activated

It is safer to refer to the correct sheet by name or codename and that problem will disappear

 

[vandango05]

Thank you for your reply.

The error I receive is this:

run-time error '-2147319784 (80028018)': method 'select' of object'_worksheet failed

Which I should have googled as hopefully i'll find the answer.

 

[Yongle]

I suspect this is a similar problem

Sub deleteimages()
    Dim s As String
    Dim pic As Picture
    Dim rng As Range    
    ' Set ws = ActiveSheet
    Set ws = ActiveWorkbook.Worksheets("Quotation")
    Set rng = ws.Range("A26:L8000")

    For Each pic In[COLOR=#ff0000] ActiveSheet.Pictures[/COLOR]
    With pic
        s = .TopLeftCell.Address & ":" & .BottomRightCell.Address
    End With
    If Not Intersect(rng, ws.Range(s)) Is Nothing Then
        pic.Delete
    End If
Next


End Sub

Your code suggests that everything should refer to sheet Quotation

So perhaps (untested)

For Each pic In[COLOR=#ff0000] ws.Pictures[/COLOR]

 

[vandango05]

Sadly that failed. Definitely calling the ActiveSheet is causing the problems, frustrating as i've never had the issue with Excel and i've used ActiveSheet alot

 

[Fluff]

The error I receive is this:

run-time error '-2147319784 (80028018)': method 'select' of object'_worksheet failed

That error makes no sense with the code you have provided, as you are not selecting anything.
Are you sure that is the code that is failing & that that is the correct error message?

 

[vandango05]

Ok here is what happens. I run this:

Sub finalisequote()'
' finalisequote Macro
'
deleteimages


Sheets("Customer Data").Select
    Range("K31:l31").Select
    Selection.Copy
    Range("K32:l32").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Quotation").Select
    
     Range("X3").Select
    Calculate
    ActiveCell.FormulaR1C1 = Date
   


    hoistnoteetc
        
    ActiveSheet.Range("$K$5:$K$7475").AutoFilter Field:=1, Criteria1:=">0", _
        Operator:=xlAnd
   
       
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = "$A$1:$I$7475"
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .FitToPagesWide = 1
        .FitToPagesTall = 30
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = True
        .DifferentFirstPageHeaderFooter = True
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        
    End With
    
   
    
    Application.PrintCommunication = True
        Sheets("Customer Data").Select
     Range("G31").Select
    ActiveCell.FormulaR1C1 = "On"
    Sheets("Quotation").Select
    
       
        ActiveWindow.View = xlPageBreakPreview
        
   
End Sub

and get the error Run-time error '-2147417848 (80010108)': Automation error The object invoked has disconnected from its clients.

Debug goes to line

ActiveSheet.Range("$K$5:$K$7475").AutoFilter Field:=1, Criteria1:=">0", _
        Operator:=xlAnd

I press stop and then run the same macro and then get the object error

 

[Yongle]

Selecting everything is not required and is less reliable (not because it does not work, it does, but more other things need to be correct at the time the code is running)

instead of

Sheets("Customer Data").Select
Range("K31:l31").Select
Selection.Copy
Range("K32:l32").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

try

With Sheets("Customer Data").Range("K31:l31")
    .Copy
    .Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

 

[Fluff]

What is "hoistnoteetc"?
Also how are you calling the macro?

 

[vandango05]

I'm calling the Macro with a button, hoistnoteetc is just the name of another macro that runs at that stage of the code which appears to work fine.