问是否有一个VBA函数可以为员工工作的天数创建布尔值？

EN

Public Function CreateMyPivotTable()

''' Source, destination, and pivot table name variables. '''
''' We're using Sheet1 and Sheet2 for our worksheets, but you
''' could probably add those as a parameter.
Dim source_worksheet_name As String: source_worksheet_name = "Sheet1"
Dim source_tablename As String: source_tablename = "timesheet"
Dim destination_worksheet_name As String: destination_worksheet_name = "Sheet2"
Dim pivot_table_name As String: pivot_table_name = "PivotTimeSheet"


Dim source_worksheet As Worksheet
Dim destination_worksheet As Worksheet

''' Create source and destination worksheet objects.
Set source_worksheet = ActiveWorkbook.Worksheets(source_worksheet_name)
Set destination_worksheet = ActiveWorkbook.Worksheets(destination_worksheet_name)

''' Create source and pivot table objects
Dim src_tbl As ListObject
Dim pivot_tbl As PivotTable


''' Focus on destinaton worksheet.
source_worksheet.Select

''' Set source table variable.
Set src_tbl = source_worksheet.ListObjects(source_tablename)


''' Clear out pivot table (function is below.
Call RemovePivotTable(pivot_table_name)

''' Generate pivotcache using our table source.
With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=src_tbl.Range.Address(False, False, xlA1, xlExternal))
    ''' Create pivot table at A1 in the destination worksheet.
    Call .CreatePivotTable(TableDestination:=destination_worksheet.Range("A1"), tablename:=pivot_table_name)
End With

''' Focus on destinaton worksheet.
destination_worksheet.Select

''' Set EmployeeName and Date in as Row and Column fields
''' Use count of Date in per EmployeeName as value.
With destination_worksheet.PivotTables(pivot_table_name)
    ''' Miss me with that Grand Total stuff
    .RowGrand = False
    .ColumnGrand = False
    
    ''' Fill any missing values with Zero.
    .NullString = "0"
    
    ''' General pivot action. Pretty self explanatory.
    With .PivotFields("EmployeeName")
        .Orientation = xlRowField
        .Position = 1
    End With

    With .PivotFields("Date in")
        .Orientation = xlColumnField
        .Position = 1
    End With

    ''' Data field section
    With .PivotFields("Date in")
        .Orientation = xlDataField
        .Function = xlCount ' Count dates per employee
        .NumberFormat = "0" ' General number format.
    End With
End With

''' Clear objects before leaving.
Set source_worksheet = Nothing
Set destination_worksheet = Nothing
Set src_tbl = Nothing
Set pivot_tbl = Nothing

End Function

Public Function RemovePivotTable(pivot_table_name As String)
''' Delete a pivot table by name.
''' Ex. usage -
'''     RemovePivotTable("PivotTimeSheet")

If Len(pivot_table_name) > 0 Then
    ''' In general, iterate over each worksheet, 
    ''' look for a name match
    ''' then clear out that pivot table.
    Dim pvttbl As PivotTable
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        For Each pvttbl In ws.PivotTables
            If pvttbl.Name = pivot_table_name Then
                pvttbl.TableRange2.Clear
            End If
        Next pvttbl
    Next ws
    Set pvttbl = Nothing
    Set ws = Nothing
End If

End Function

ActiveSheet.Range("timesheet[#All]").RemoveDuplicates Columns:=Array(2, 3), Header:=xlYes