Hi guys, can anyone explain why the following case statement isnt working? Intead of replacing ALL with * it just skips straight to the else statement and uses all.
Thanks in advance
Code:
Sub Macro1()
Dim startdate As Long, enddate As Long, Client As String, Depot As String
startdate = CLng(DateValue(Sheets("Reports & Archive").Range("B8")))
enddate = CLng(DateValue(Sheets("Reports & Archive").Range("D8")))
Select Case Sheets("Reports & Archive").Range("H8").Value
Case "ALL"
Client = "*"
Case Else
Client = Sheets("Reports & Archive").Range("H8").Value
End Select
Select Case Sheets("Reports & Archive").Range("F8").Value
Case "ALL"
Depot = "*"
Case Else
Depot = Sheets("Reports & Archive").Range("F8").Value
End Select
Sheets("Archived Escalations").Activate
Sheets("Archived Escalations").Copy
ChDir "[URL="file://\\888888888888888888888"]\\888888888888888888888[/URL]"
ActiveWorkbook.SaveAs Filename:= _
"[URL="file://\\88888888888888888888"]\\88888888888888888888[/URL]report.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveSheet.Unprotect
Range("B2:J2").Select
Selection.AutoFilter Field:=1, Criteria1:=">=" & startdate, Operator:=xlAnd _
, Criteria2:="<=" & enddate
Selection.AutoFilter Field:=5, Criteria1:="" & Client
Selection.AutoFilter Field:=6, Criteria1:="" & Depot
End Sub
Thanks in advance