copy cell data to another sheet based on month entered

Hello,

on the attached worksheet i'm trying to get the data from "Closed Business" to The "Executive" sheet when the Month, for example "October" is entered into cell J2 on the Executive sheet, (I entered the first row in as an example) This would then save as "Closed Business for Distribution - October, 2016" , or whatever month is entered in J2, in a file to my desktop. I have some similar codes that I have got from here, but am not advanced enough to know how to tweak them to this worksheet.

Thank you for any and all help.

Sub test()
Dim mydate As Date, lr As Long, lr2 As Long
With Sheets("Closed Business")
    lr = .Range("C" & Rows.Count).End(xlUp).row
    arr = .Range("C4", "Q" & lr)
    With Sheets("Executive")
        mydate = "1/" & .Range("J2") & "/" & .Range("K2")
        lr2 = .Range("B" & Rows.Count).End(xlUp).row
        If lr2 > 7 Then .Range("B8", "J" & lr2).ClearContents
        ReDim sn(1 To UBound(arr), 1 To 9)
        i = 1
        For x = 1 To UBound(arr)
            If Month(arr(x, 15)) = Month(mydate) And Year(arr(x, 15)) = Year(mydate) Then
                sn(i, 1) = arr(x, 4)
                sn(i, 2) = arr(x, 5)
                sn(i, 3) = arr(x, 6)
                sn(i, 4) = arr(x, 7)
                sn(i, 5) = arr(x, 8)
                sn(i, 6) = arr(x, 1)
                sn(i, 7) = arr(x, 3)
                sn(i, 8) = arr(x, 9)
                sn(i, 9) = arr(x, 15)
                i = i + 1
            End If
        Next
        .Range("B8").Resize(UBound(sn), UBound(sn, 2)) = sn
    End With
End With
End Sub

Kind regards
Leo

Thanks Leo, Appreciate it!!

Very well Noted, Thanks for the tip John.