dpaton05
Well-known Member
- Joined
- Aug 14, 2018
- Messages
- 2,352
- Office Version
- 365
- 2016
- Platform
- Windows
I have a spreadsheet and I try and run the following code in module 4
I get the error Method intersect of object global failed and if I press debug, the following line of code is highlighted:
which is within the following sub in the ThisWorkbook module
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.
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
VBA Code:
If Not Intersect(Target, PO) Is Nothing Then
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.