Excel打开时自动弹出输入框并创建指定列字段的可筛选数据透视表的VBA实现问询

嘿，我看你想要实现的是：当Excel文件打开时自动弹出输入框，让你指定一个列类别，然后基于整个数据集在新工作表创建一个透视表，把你指定的列设为透视表的分类字段，同时保留整个数据集的筛选能力。我帮你调整并优化了原有的VBA代码，让它更贴合你的需求，同时修正了一些小问题：

第一步：配置文件打开时的输入框逻辑 #

这段代码会在Excel文件打开时自动弹出输入框，验证你输入的列字段是否存在于数据源中，确认后自动触发透视表创建：

Option Explicit

' 全局变量：存储用户输入的透视表类别字段名
Private pivotCategoryField As String

Private Sub Workbook_Open()
    Dim inputValue As Variant
    Dim wsData As Worksheet
    Dim isFieldExists As Boolean
    
    ' 指定你的数据源工作表（改成你实际的表名）
    Set wsData = ThisWorkbook.Worksheets("Data")
    
ReShowInputBox:
    ' 弹出输入框，提示用户输入目标字段名
    inputValue = Application.InputBox("请输入要作为透视表类别的列字段名（如Sales Person、Loc等）：", "透视表类别设置")
    
    ' 用户点击取消则退出流程
    If inputValue = False Then Exit Sub
    
    ' 检查输入的字段是否存在于数据源表头（第一行）
    isFieldExists = False
    On Error Resume Next
    isFieldExists = Not wsData.Rows(1).Find(inputValue, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing
    On Error GoTo 0
    
    If isFieldExists Then
        pivotCategoryField = inputValue
        MsgBox "已确认字段：" & pivotCategoryField & vbCrLf & "即将创建透视表。"
        ' 自动调用透视表创建宏
        createPivotTableNewSheet
    Else
        MsgBox "输入的字段名不存在于数据源中，请重新输入！", vbExclamation
        GoTo ReShowInputBox
    End If
End Sub

第二步：创建带筛选功能的定制透视表 #

这段代码会基于你输入的字段创建透视表，自动识别数据源范围，同时开启多重筛选功能：

Sub createPivotTableNewSheet()
    ' 声明变量：定义数据源范围的行/列边界
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myFirstColumn As Long
    Dim myLastColumn As Long
    
    ' 声明变量：存储数据源和目标区域地址
    Dim mySourceData As String
    Dim myDestinationRange As String
    
    ' 声明对象变量：工作表、透视表实例
    Dim mySourceWorksheet As Worksheet
    Dim myDestinationWorksheet As Worksheet
    Dim myPivotTable As PivotTable
    
    ' 检查用户是否已输入有效字段
    If pivotCategoryField = "" Then
        MsgBox "请先在打开文件时输入要作为透视表类别的字段名！", vbExclamation
        Exit Sub
    End If
    
    ' 指定数据源工作表，创建新表作为透视表载体
    With ThisWorkbook
        Set mySourceWorksheet = .Worksheets("Data")
        Set myDestinationWorksheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
    End With
    
    ' 设置透视表起始位置
    myDestinationRange = myDestinationWorksheet.Range("A1").Address(ReferenceStyle:=xlR1C1)
    
    ' 自动识别数据源的实际范围（避免固定行数浪费资源）
    With mySourceWorksheet
        myFirstRow = 1
        myLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row ' 从A列获取最后一行数据
        myFirstColumn = 1
        myLastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column ' 从第一行获取最后一列数据
    End With
    
    ' 拼接数据源完整地址
    With mySourceWorksheet.Cells
        mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
    End With
    
    ' 创建透视表缓存并生成透视表
    Set myPivotTable = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=mySourceWorksheet.Name & "!" & mySourceData _
    ).CreatePivotTable( _
        TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, _
        TableName:="CustomPivotTable" _
    )
    
    ' 配置透视表字段
    With myPivotTable
        ' 将用户指定的字段设为行类别
        .PivotFields(pivotCategoryField).Orientation = xlRowField
        .PivotFields(pivotCategoryField).Position = 1
        
        ' 配置数值字段（可根据你的需求修改/增减）
        With .PivotFields("Inv Qty")
            .Orientation = xlDataField
            .Position = 1
            .Function = xlSum
            .NumberFormat = "#,##0.00"
            .Caption = "总库存数量" ' 自定义显示名称
        End With
        With .PivotFields("Unit Price")
            .Orientation = xlDataField
            .Position = 2
            .Function = xlSum
            .NumberFormat = "$#,##0.00"
            .Caption = "总金额" ' 自定义显示名称
        End With
        
        ' 启用多重筛选功能，方便对数据集进行筛选
        .RowAxisLayout xlTabularRow
        .AllowMultipleFilters = True
    End With
    
    ' 重命名透视表工作表并激活
    myDestinationWorksheet.Name = "自定义透视表"
    myDestinationWorksheet.Activate
End Sub

关键说明 #

• 自动识别数据源范围：不再固定100万行，而是根据实际数据量获取边界，提升运行效率
• 字段有效性验证：避免输入不存在的列字段导致报错
• 可定制数值字段：你可以根据自己的需求修改透视表中的数值汇总字段（比如改成计数、平均值等）
• 多重筛选支持：默认开启透视表的多条件筛选功能，方便你对整个数据集进行灵活筛选

备注：内容来源于stack exchange，提问作者James Tesarek