Option Explicit
Const ClientFullName As String = "full_file_name_ here"

'   Create Timesheet Excel File for each Customer
Sub AdvancedFilterCopyforExcel()
Dim clientWB As Workbook
Dim clientWS As Worksheet
Dim TimeSheet As Worksheet

Set TimeSheet = Sheet7
Set clientWB = Workbooks.Open(ClientFullName)
Set clientWS = ActiveSheet


' AdvFilterCopyTimesheet Macro
    Sheet9.Range("M9:V1820").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("'Timesheet Log'!Criteria"), CopyToRange:=Range("'Timesheet Log'!Extract")

'MANUALLY SAVE EACH CUSTOMERS COPY AS ITS OWN excel SHEET in same  FOLDER AS FILE
    With clientWS
        .Range("B4:C4").Value = TimeSheet.Range("B4:C4").Value
        .Range("B5").Value = TimeSheet.Range("B5").Value
        .Range("A7:H1819").Value = TimeSheet.Range("A7:H1819").Value
        .Range("G1825").Select
    End With

    With TimeSheet
        .ShowAllData
        .Range("K1").CurrentRegion.AutoFilter Field:=1, Criteria1:="1"

    ' unhideemptyrows Macro
        .Range("L1").CurrentRegion.AutoFilter Field:=1
        .Range("B4:C4").ClearContents
    End With
    Sheet9.Range("Z10:AI1820").ClearContents

End Sub