Activate A Workbook After It Is Open

[xander1981]

Hello, Please can someone help as I am about to through my computer out of the window and its a work machines so probably not a great idea. I have been trying to simply open a workbook and activate it so I can perform some filtering etc. When I run this from the code file there is no problem at all but when I come out of the file and click the workbook shortcut (as users would) I get an error stating "Method 'activate' of '_Object Workbook' failed. Not sure where i am going wrong with such a simple task. Any help would really help me. The code breaks on the line below I have in Red font.

Dim ShipmentTimelinessWeek As Workbook
Application.ScreenUpdating = False
If Dir("\\2.44.120.20\Felixstowe\Team KH\Timeliness report week.xlsx") = "" Then
MsgBox ("The Shipment Timeliness Report (GTN) was not found" & vbNewLine & _
"please make sure the file is saved to:" & vbNewLine & _
"\\2.44.120.20\Felixstowe\Team KH\Input Reports\")
Else
Set ShipmentTimelinessWeek = Workbooks.Open("\\2.44.120.20\Felixstowe\Team KH\Timeliness report week.xlsx")
End If
'***************************************************************************
'Delete any rows from Timeliness Report than are outside the date range
Workbooks("Timeliness report week.xlsx").Activate
With ActiveWorkbook.Worksheets("Shipment Timeliness")
.AutoFilterMode = False
    With Range("F1", Range("F" & Rows.Count).End(xlUp))
        .AutoFilter 1, Criteria1:="<" & GetDateRangeFrom, Operator:=xlOr, Criteria2:=">" & GetDateRangeTo
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With

[AerosAtar]

Not 100% sure you need to activate it (since it is already active), but you could try using ThisWorkbook.Activate instead.

[bakerman]

In case the file isn't found, you need to provide an exit so the code stops. Now you only give a messagebox saying the file isn't found but after that the code continues so you recieve an errormessage.

MsgBox ("The Shipment Timeliness Report (GTN) was not found" & vbNewLine & _
"please make sure the file is saved to:" & vbNewLine & _
"\\2.44.120.20\Felixstowe\Team KH\Input Reports\"):Exit sub

[xander1981]

Thanks guys, both responses correct, its the 'WITH' statement that seems to no longer work for me now. Not sure what I have done to my excel programme as used to do With sheet **** do this END WITH but now doesnt work?? v strange

[snb]

sub M_snb()
  If Dir("\\2.44.120.20\Felixstowe\Team KH\Timeliness report week.xlsx") <>"" Then
   with Workbooks.Open("\\2.44.120.20\Felixstowe\Team KH\Timeliness report week.xlsx")
      with .sheets("Shipment Timeliness").usedrange.columns(6)
           .AutoFilter 1, "<" & GetDateRangeFrom, xlOr, ">" & GetDateRangeTo
           .Offset(1).SpecialCells(12).EntireRow.Delete
           .autofilter
       end with
    End With
  end if
End With