Excel VBA创建数据透视表报运行时错误5（无效过程调用或参数）求助

核心错误点 #

• PivotCache创建时仅传入数据源的地址字符串，未关联对应工作表，当活动工作表不是数据源所在的Worksheets(1)时，会触发参数无效错误
• 第二段剪切逻辑的范围写法错误：Range("C49:A" & lastrow)列顺序颠倒，Excel无法识别起始列大于结束列的范围
• lastrow声明为Integer类型，最大仅支持32767行，数据量超过时会溢出触发错误
• 需提前确认数据源首行存在名为loc的列，否则访问PivotFields("loc")也会触发参数无效

修复后的代码 #

Sub 生成透视表报告()
    Dim pc As PivotCache
    Dim pt As PivotTable
    Dim pf As PivotField
    ' 改用Long类型存储行号，支持最大1048576行
    Dim lastrow As Long
    Dim wsSource As Worksheet
    Dim wsPivot As Worksheet
    
    ' 提前绑定数据源工作表，避免活动工作表切换导致的引用错误
    Set wsSource = ThisWorkbook.Worksheets(1)
    
    ' 创建PivotCache时直接传入Range对象，无需手动拼接地址
    Set pc = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=wsSource.Range("A1").CurrentRegion)
    
    ' 新建工作表存放透视表，提前绑定避免ActiveCell引用异常
    Set wsPivot = ThisWorkbook.Worksheets.Add
    Set pt = pc.CreatePivotTable(wsPivot.Range("A1"), "EmptyLocationsPivot")
    
    Set pf = pt.PivotFields("loc")
    pf.Orientation = xlRowField
    
    ' 取数据源工作表的最大行号
    lastrow = wsSource.Range("A" & wsSource.Rows.Count).End(xlUp).Row
    
    ' 复制透视表行字段值到新表
    wsPivot.Range("A2:A" & lastrow - 1).Copy
    ThisWorkbook.Worksheets.Add.Range("A1").PasteSpecial xlPasteValues
    
    ' 回到数据源工作表处理拆分逻辑
    wsSource.Activate
    lastrow = wsSource.Range("A" & wsSource.Rows.Count).End(xlUp).Row
    
    If lastrow > 48 Then
        wsSource.Range("A49:A" & lastrow).Cut Destination:=wsSource.Range("C1")
        Application.CutCopyMode = False
    End If
    
    lastrow = wsSource.Range("C" & wsSource.Rows.Count).End(xlUp).Row
    If lastrow > 48 Then
        ' 修正范围列顺序错误，从C49开始到C列最后一行
        wsSource.Range("C49:C" & lastrow).Cut Destination:=wsSource.Range("D1")
        Application.CutCopyMode = False
    End If
    
    MsgBox "Report Is Ready for Print!"
End Sub

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