Pivot table报"subscript out of range"错误，如何适配动态行数？

你遇到的"subscript out of range"错误核心原因有两个：

• 宏里硬编码了固定的数据源连接范围WorksheetConnection_Sheet1!$A$1:$H168，一旦表格行数变化或切换到其他表格，这个固定范围的连接要么覆盖不全数据，要么直接不存在
• 依赖Select操作和固定工作表名称（比如Sheet2），工作表状态或名称变化就会触发错误

以下是修改后的适配动态行数的宏代码：

Sub autopivot()
    Dim dataWs As Worksheet
    Dim pivotWs As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim dataRange As Range
    Dim pivotCache As PivotCache
    Dim pivotTable As PivotTable
    Dim connName As String
    
    ' 指定数据源工作表（可改成固定名称如Sheets("Sheet1")，这里用当前激活表更灵活）
    Set dataWs = ActiveSheet
    connName = "DynamicDataConn_" & dataWs.Name
    
    ' 删除已存在的同名连接，避免冲突
    On Error Resume Next
    ActiveWorkbook.Connections(connName).Delete
    On Error GoTo 0
    
    ' 动态获取完整数据范围
    lastRow = dataWs.Cells(dataWs.Rows.Count, "A").End(xlUp).Row
    lastCol = dataWs.Cells(1, dataWs.Columns.Count).End(xlToLeft).Column
    Set dataRange = dataWs.Range(dataWs.Cells(1, 1), dataWs.Cells(lastRow, lastCol))
    
    ' 处理透视表存放工作表：存在则复用，不存在则新建
    On Error Resume Next
    Set pivotWs = ThisWorkbook.Sheets("PivotSheet")
    If Err.Number <> 0 Then
        Set pivotWs = ThisWorkbook.Sheets.Add(After:=dataWs)
        pivotWs.Name = "PivotSheet"
    End If
    On Error GoTo 0
    
    ' 创建基于动态范围的透视缓存
    Set pivotCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=dataRange)
    
    ' 生成透视表
    Set pivotTable = pivotCache.CreatePivotTable( _
        TableDestination:=pivotWs.Range("A3"), _
        TableName:="PivotTable1", _
        DefaultVersion:=6)
    
    ' --------------------------
    ' 可添加自定义透视表字段设置，示例：
    ' With pivotTable.PivotFields("你的行字段标题")
    '     .Orientation = xlRowField
    '     .Position = 1
    ' End With
    ' With pivotTable.PivotFields("你的值字段标题")
    '     .Orientation = xlDataField
    '     .Function = xlSum
    '     .Name = "求和结果"
    ' End With
    ' --------------------------
End Sub

关键修改说明 #

• 动态范围适配：用End(xlUp)和End(xlToLeft)自动定位数据的最后一行/列，不管表格行数怎么变都能覆盖完整数据源
• 抛弃固定连接：改用xlDatabase类型的透视缓存，直接绑定动态数据范围，不需要依赖录制生成的固定路径连接
• 避免工作表冲突：先检查透视表存放工作表是否存在，不存在再新建，解决原宏中Sheet2已存在导致的错误
• 移除Select操作：全程用对象变量操作，不依赖当前激活状态，大幅降低出错概率
• 清理旧连接：提前删除同名连接，防止重复创建引发的冲突

内容的提问来源于stack exchange，提问作者Makinmacros