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