I need to go 2 levels above

[dpaton05]

I have this code that is trying to open CSS_quoting_tool_29.5 by moving up 2 levels of folders then looking for the file. What is wrong with my code?

Sub LateCancel()

        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet, wb As Workbook, wb2 As Workbook, WbPath As String, QTPath As String
        Set wb2 = ThisWorkbook
        Set sh = Sheets("Totals")
        'Set sht = Sheets("Cancellations")
        Dim LCReq As String: LCReq = sh.[B32].Value
        Dim LCDt As String: LCDt = sh.[B34].Value
        WbPath = ThisWorkbook.Path
        QTPath = Left(WbPath, InStrRev(WbPath, "\") - 2)

Application.ScreenUpdating = False
        'If Not isFileOpen(DocYearName & ".xlsm") Then Workbooks.Open ThisWorkbook.Path & "\" & "Work Allocation Sheets" & "\" & Site & "\" & DocYearName & ".xlsm"
        
        If Not isFileOpen("CSS_quoting_tool_29.5.xlsm") Then Workbooks.Open QTPath & "\" & "CSS_quoting_tool_29.5.xlsm"
    
        For Each ws In 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

 

[dpaton05]

I got that working with

Sub LateCancel()

        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet, wb As Workbook, wb2 As Workbook, WbPath As String, QTPath As String
        Set wb2 = ThisWorkbook
        Set sh = Sheets("Totals")
        'Set sht = Sheets("Cancellations")
        Dim LCReq As String: LCReq = sh.[B32].Value
        Dim LCDt As String: LCDt = sh.[B34].Value
        WbPath = ThisWorkbook.Path
        'CurDir ".."
        'CurDir ".."
        'QTPath = Left(WbPath, InStrRev(WbPath, "\") - 2)
        QTPath = ThisWorkbook.Path & "\..\" & "\..\"
Application.ScreenUpdating = False
        'If Not isFileOpen(DocYearName & ".xlsm") Then Workbooks.Open ThisWorkbook.Path & "\" & "Work Allocation Sheets" & "\" & Site & "\" & DocYearName & ".xlsm"
        
        If Not isFileOpen("CSS_quoting_tool_29.5.xlsm") Then Workbooks.Open QTPath & "\" & "CSS_quoting_tool_29.5.xlsm"
    
        For Each ws In 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

 

[fhqwgads]

whenever i need to move up folders i use ChDir
so like

ChDir ActiveWorkbook.Path
ChDir ".."
ChDir ".."

should move you up two folders from where your sheet is. then i think use CurDir to reference that location

ChDir statement (VBA)

Office VBA reference topic

docs.microsoft.com