如何使输入框要求在打开 excel 文件时立即创建数据透视表的值？

问：

我有一个包含大量数据（行和列）的 Excel 工作表 我正在尝试编写一个 VBA 代码，该代码将打开一个输入框，该输入框采用列类别并将其放入新工作表中数据透视表上的类别中。 数据透视表包含可以使用其他值筛选的整个数据集。

Option Explicit

Private Sub workbook_open()

    Dim cellvalue As Variant
    Dim ws As Worksheet

    Set ws = Worksheets("Data Sheet")

ReShowInputBox:     cellvalue = Application.InputBox("Sales Person:  ")

    If cellvalue = False Then Exit Sub
    If isvalue(cellvalue) = isvalue(cellvalue) Then
        ws.Range("A1").Value = DateValue(cellvalue)
    Else: MsgBox ("Invalid Date!")
    GoTo ReShowInputBox
    
End If

End Sub



Sub createPivotTableNewSheet()

    'Declare variables to hold row and column numbers that define source data cell range
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myFirstColumn As Long
    Dim myLastColumn As Long
 
    'Declare variables to hold source and destination cell range address
    Dim mySourceData As String
    Dim myDestinationRange As String
 
    'Declare object variables to hold references to source and destination worksheets, and new Pivot Table
    Dim mySourceWorksheet As Worksheet
    Dim myDestinationWorksheet As Worksheet
    Dim myPivotTable As PivotTable
 
    'Identify source and destination worksheets. Add destination worksheet
    With ThisWorkbook
        Set mySourceWorksheet = .Worksheets("Data")
        Set myDestinationWorksheet = .Worksheets.Add
    End With
 
    'Destination cell range
    myDestinationRange = myDestinationWorksheet.Range("A1").Address(ReferenceStyle:=xlR1C1)
 
    'Row and column numbers that define source data cell range
    myFirstRow = 1
    myLastRow = 1000000
    myFirstColumn = 1
    myLastColumn = 25
 
    'Source data cell range
    With mySourceWorksheet.Cells
        mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
    End With
 
    'Create Pivot Table report based on cache that was created
    Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="PivotTableNewSheet")
 
    'Edit Pivot Table fields
    With myPivotTable
        .PivotFields("Loc").Orientation = xlRowField
        With .PivotFields("Inv Qty")
            .Orientation = xlDataField
            .Position = 1
            .Function = xlSum
            .NumberFormat = "#,##0.00"
        End With
        With .PivotFields("Unit Price")
            .Orientation = xlDataField
            .Position = 2
            .Function = xlSum
            .NumberFormat = "$#,##0.00"
        End With
    End With
 
Sheets("Sheet8").Select
Sheets("sheet8").Name = "New Pivot Table"

 
End Sub