Macro to filter report based on each change in a column and save copies

[sebasp]

It has been a long time since I have written a macro and could use some help.
I have a master file and I need to be able to filter the report by department and save a copy of just that departments information to our network.
using column A we can say is the department number.

Thank you

 

[sebasp]

I have tried this so far and I am getting a debug error at: ActiveWorkbook.SaveAs Pth & Ky, 52

suggestions?

Sub Test()
Dim Ws As Worksheet
Dim Cl As Range
Dim Ky As Variant
Dim Pth As String

Application.ScreenUpdating = False
Pth = "C:\Mrexcel\test\"
Set Ws = Sheets("sheet1")
With CreateObject("scripting.dictionary")
For Each Cl In Ws.Range("f2", Ws.Range("f" & Rows.Count).End(xlUp))
.Item(Cl.Value) = Empty
Next Cl
For Each Ky In .Keys
Ws.Copy
Range("A1:Q1").AutoFilter 2, "<>" & Ky
ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
ActiveWorkbook.SaveAs Pth & Ky, 52
ActiveWorkbook.Close False
Next Ky
End With
End Sub