X.Activate

[nniedzielski]

Hello All-

I am running a macro that has been working for quite some time now, but for some reason it now has a glitch, the only thing that has been changed is the share drive it has been connecting to was renamed. I has posted the code below, the line in red is where the problem is occurring, there are two files, X & Y, X will activate, however when it comes to Y activating, X stays on the screen and Y never activates.

Any help is always appreciated,

Dim thisdate As Date
    Dim x As Workbook
    Dim y As Workbook
    Dim FileY As String
    Dim j As Long
    Dim i As Long
    Dim k As Long
    Dim r As Variant
    Dim Found As Range
    Dim NewPath As String, OldFile As String, NewFile As String
    
    thisdate = Date
    FileMonth = Month(thisdate)
    thisMonth = MonthName(FileMonth)
    FileY = "[URL="file://\\Usbtrwdcnhm5mz1\XPO\DOCK"]\\Usbtrwdcnhm5mz1\XPO\DOCK[/URL] INBOUNDS\ITA/ITA.xls"
    Select Case True
        Case Len(Dir(FileY)) > 0
            Workbooks.Open FileY
        Case Len(Dir("J" & Right(FileY, Len(FileY) - 1))) > 1
            FileY = "J" & Right(FileY, Len(FileY) - 1)
            Workbooks.Open FileY
        Case Len(Dir("I" & Right(FileY, Len(FileY) - 1))) > 1
            FileY = "J" & Right(FileY, Len(FileY) - 1)
            Workbooks.Open FileY
        Case Else
    End Select
        
    If Not Dir(FileY, vbDirectory) = vbNullString Then
        Set x = Workbooks.Open("[URL="file://\\Usbtrwdcnhm5mz1\XPO\DOCK"]\\Usbtrwdcnhm5mz1\XPO\DOCK[/URL] INBOUNDS\2018" & thisMonth & ".xlsx")
        Set y = Workbooks.Open(FileY)
            Rows("1:4").Delete
            Selection.AutoFilter
            ActiveSheet.Range("A:D").AutoFilter Field:=4, Criteria1:="<>MTRL - Raw", Criteria2:="<>MTRL - Service"
            ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)
            Selection.AutoFilter
            Range("E2").Formula = "=VLOOKUP(TRIM(A2),'[!! IB Macro.xlsm]Codes'!$A$1:$B$110,2,FALSE)"
                lastRow = Range("D" & Rows.Count).End(xlUp).Row
                    Range("E2").AutoFill Destination:=Range("E2:E" & lastRow)
    Else
        MsgBox "ITA File Not Found, Please Run Inbound Trailers Arrived Report and Save as ITA", 48, "File Not Found"
    Exit Sub
    
    End If
    
        Dim ColN
            ColN = Array(3, 8, 14, 19)
        Dim ColR
            ColR = Array(2, 7, 13, 18)
        x.Activate
        Worksheets(Format(Date, "mmmd")).Select
                    For k = LBound(ColN) To UBound(ColN)
                        For t = LBound(ColR) To UBound(ColR)
                                finalrow = Cells(Rows.Count, ColR(t)).End(xlUp).Row
                            For i = 5 To finalrow
                                x.Activate
                                Worksheets(Format(Date, "mmmd")).Select
                                    r = Cells(i, ColN(k)).Value
                                    s = Cells(i, ColR(t)).Value
                                [COLOR=#FF0000]y.Activate[/COLOR]

 

[Fluff]

the path for file Y is wrong, try

FileY = "\\Usbtrwdcnhm5mz1\XPO\DOCK INBOUNDS\ITA\ITA.xls"

 

[nniedzielski]

that is a good catch, but after fixing that line, the y.activate is still not activating

 

[Fluff]

You seem to be potentially opening the file in a number of places

  Select Case True
        Case Len(Dir(FileY)) > 0
            [COLOR=#ff0000]Workbooks.Open FileY[/COLOR]
        Case Len(Dir("J" & Right(FileY, Len(FileY) - 1))) > 1
            FileY = "J" & Right(FileY, Len(FileY) - 1)
           [COLOR=#ff0000] Workbooks.Open FileY[/COLOR]
        Case Len(Dir("I" & Right(FileY, Len(FileY) - 1))) > 1
            FileY = "J" & Right(FileY, Len(FileY) - 1)
           [COLOR=#ff0000] Workbooks.Open FileY[/COLOR]
        Case Else
    End Select
        
    If Not Dir(FileY, vbDirectory) = vbNullString Then
        Set x = Workbooks.Open("\\Usbtrwdcnhm5mz1\XPO\DOCK INBOUNDS\2018" & thisMonth & ".xlsx")
        [COLOR=#ff0000]Set y = Workbooks.Open(FileY)[/COLOR]

But you're only setting y=file on the last one.

 

[nniedzielski]

what would you recommend to fix this issue?

 

[Fluff]

Difficult to say as I don't understand what the Select case is trying to do.
Was the code originally opening the file from a mapped drive?

 

[nniedzielski]

yes it was, then i used the convention to open it regardless of what letter drive the user was using,

 

[Fluff]

In that case try

    thisdate = Date
    FileMonth = Month(thisdate)
    ThisMonth = MonthName(FileMonth)
    FileY = "\\Usbtrwdcnhm5mz1\XPO\DOCK INBOUNDS\ITA/ITA.xls"
    If Not Dir(FileY, vbDirectory) = vbNullString Then
        Set x = Workbooks.Open("\\Usbtrwdcnhm5mz1\XPO\DOCK INBOUNDS\2018" & ThisMonth & ".xlsx")
        Set y = Workbooks.Open(FileY)

 

[nniedzielski]

that did the trick,

thank you so much!!

 

[Fluff]

Glad to help & thanks for the feedback