Deleting objects within a specified cell range

[glad_ir]

Hi,

Please can somebody help we with this one? Scratching my head....

With macro 1 below I'm trying to delete of any objects in range AA7:AZ60 of my worksheet named COMPARISON then copy either object 22 or 23 (dependant on the value in cell AD5) and paste it into cell AB9 but I'm getting a run-time error "1004 application-defined or object-defined error" and when I debug it flags the "If Not Intersect....." line in the code as the issue.

Any help much appreciated!

thank you,
Iain

Sub macro1()

    Call Test
    If Range("AD5") = 1 Then macro2
    If Range("AD5") = 2 Then macro4
    
End Sub

Sub Test()
Dim Sh As Shape
With Worksheets("COMPARISON")
   For Each Sh In .Shapes
       If Not Application.Intersect(Sh.TopLeftCell, .Range("AA7:AZ60")) Is Nothing Then
         Sh.Delete
    End If
    Next Sh
End With
End Sub

Sub macro2()

    Call Test
    ActiveSheet.Shapes.Range(Array("Object 22")).Select
    Selection.Copy
    Range("AB9").Select
    ActiveSheet.Paste
    
End Sub

Sub macro4()

    ActiveSheet.Shapes.Range(Array("Object 23")).Select
    Selection.Copy
    Range("AB9").Select
    ActiveSheet.Paste
    
End Sub

 

[glad_ir]

OK - update on this one - very weird. The code above works as long as I haven't selected a cell with data validation before running it!

Once I've selected a data validated cell I get the error above when I run the macro.....even if the data validated cell isn't selected when the macro runs and nothing new has been selected from the data validated list. Once the macro errors I can't reset it. If I close the workbook and reopen the macro works fine again.....until I select a data validated cell! The data validated cells are all outside the range in the code.

A weird one for a Saturday night!

Any help appreciated

thanks,
Iain