使用宏创建数据透视表时出现Run time error 1004错误求助

Let's walk through the issues in your code and fix the error step by step:

Common Causes of the Error #

Your macro is hitting an Application-defined or object-defined error due to a few key issues:

• Overlapping source data and pivot table range: You're trying to place the pivot table in the same worksheet as your source data, starting at row 2—which overlaps with your source data (rows 1 to LastRow). Excel blocks pivot tables from overlapping their source datasets.
• Unnecessary worksheet activation: Using Activate is risky and can cause errors if the worksheet isn't actually active; better to work directly with object references.
• Combined cache/table creation: Chaining CreatePivotCache and CreatePivotTable in one line can sometimes lead to object reference issues, making debugging harder.

Corrected Code #

Here's the revised macro with fixes and comments explaining each change:

Sub CreatePivotTable()
    Dim DSheet As Worksheet
    Dim LastRow As Long, LastCol As Long
    Dim PRange As Range
    Dim PivotCache As PivotCache
    Dim PivotTable As PivotTable
    Dim PivotDest As Range
    
    ' Set reference to your data worksheet (no need to activate)
    Set DSheet = ActiveWorkbook.Worksheets("Sheet1")
    
    ' Get last row/column of source data
    LastRow = DSheet.Cells(DSheet.Rows.Count, 1).End(xlUp).Row
    LastCol = DSheet.Cells(1, DSheet.Columns.Count).End(xlToLeft).Column
    
    ' Define source data range
    Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
    
    ' Set pivot table destination to a spot that doesn't overlap source data
    ' We'll place it starting at row 1, next column after source data (safer)
    Set PivotDest = DSheet.Cells(1, LastCol + 2)
    
    ' Check if pivot table already exists and delete it to avoid naming conflicts
    On Error Resume Next
    Set PivotTable = DSheet.PivotTables("PivotTable")
    If Not PivotTable Is Nothing Then
        PivotTable.TableRange2.Delete
    End If
    On Error GoTo 0
    
    ' Create pivot cache separately for better control
    Set PivotCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=PRange, _
        Version:=xlPivotTableVersion15)
    
    ' Create the pivot table from the cache
    Set PivotTable = PivotCache.CreatePivotTable( _
        TableDestination:=PivotDest, _
        TableName:="PivotTable", _
        DefaultVersion:=xlPivotTableVersion15)
    
    ' Optional: Add fields to your pivot table (example)
    ' With PivotTable.PivotFields("YourFieldName")
    '     .Orientation = xlRowField
    '     .Position = 1
    ' End With
End Sub

Key Fixes Explained #

• Non-overlapping destination: The pivot table now starts at row 1 of the next column after your source data, ensuring no overlap with the original dataset.
• Removed Activate: We work directly with the DSheet object instead of relying on activating the worksheet, which makes the macro more reliable.
• Separate cache/table creation: Splitting these steps makes it easier to debug if either part fails.
• Pivot table existence check: Deletes any existing pivot table with the same name to avoid naming conflicts that would throw errors.

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