How do I make an input box ask for a value that creates a pivot table immediately when the excel file is opened?

Question

I have an excel sheet with a large amount of data (rows and columns) I am trying to write a VBA code that will open an input box that takes a column category and makes it into a category on a pivot table in a new sheet. The pivot table contains the entire dataset which can be filtered with other values.

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