Method intersect of object global failed

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,352
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a spreadsheet and I try and run the following code in module 4

VBA Code:
Sub LateCancel()

        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet, QT As String, wb2 As Workbook, WbPath As String, QTPath As String
        Set wb2 = ThisWorkbook
        QT = "CSS_quoting_tool_29.5.xlsm"
        Set sh = wb2.Worksheets("Totals")
        'Set sht = Sheets("Cancellations")
        Dim LCReq As String: LCReq = sh.Cells(32, 2).Value
        Dim LCDt As String: LCDt = sh.Cells(34, 2).Value
        WbPath = ThisWorkbook.Path
        'CurDir ".."
        'CurDir ".."
        'QTPath = Left(WbPath, InStrRev(WbPath, "\") - 2)
        QTPath = ThisWorkbook.Path & "\..\" & "\..\"
Application.ScreenUpdating = False     
        If Not isFileOpen(QT) Then Workbooks.Open QTPath & "\" & QT
   
        For Each ws In wb2.Worksheets
                If ws.Name <> "Cancellations" And ws.Name <> "Totals" Then
                        With ws.[A3].CurrentRegion
                                .AutoFilter 1, LCDt
                                .AutoFilter 3, LCReq
                                .Offset(1).EntireRow.Copy sh.Range("A" & Rows.Count).End(xlUp).Offset(1)
                                .Offset(1).EntireRow.Delete
                                .AutoFilter
                        End With
                End If
        Next ws
       
'sh.Range("B32,B34").ClearContents
Application.ScreenUpdating = True
End Sub
I get the error Method intersect of object global failed and if I press debug, the following line of code is highlighted:
VBA Code:
If Not Intersect(Target, PO) Is Nothing Then
which is within the following sub in the ThisWorkbook module
VBA Code:
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    Dim Req As Range, PO As Range, CancelReq As Range, CancelDate As Range
   
    Select Case WorksheetFunction.Proper(sh.Name)
        Case "Totals" 'Case "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December", "Cancellations"
            Set Req = Range("B18")
            Set PO = Range("B20")
            If Not Intersect(Target, PO) Is Nothing Then
                Application.EnableEvents = False
                If PO <> "" And Req <> "" Then Call UpdateEverySheet(Req, PO)
                PO.ClearContents
                Req.ClearContents
                Application.EnableEvents = True
            End If
    End Select
End Sub

Can someone help me with this error please?

I forgot to mention, it seems to work if the workbook defined by QT in the sub LateCancel is already open but I get the error if the workbook is not open.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,215,063
Messages
6,122,930
Members
449,094
Latest member
teemeren

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