在数据透视表中创建动态范围

问题描述：

我试图将我的固定范围（A1：G4193）更改为动态，因为需要每天输入新数据。在数据透视表中创建动态范围

这里是我的代码：

Sub Create_Pivot() 

Dim sht As Worksheet 
Dim pvtCache As PivotCache 
Dim pvt As PivotTable 
Dim StartPvt As String 
Dim SrcData As String 
Dim pf As PivotField 

SrcData = ActiveSheet.Name & "!" & Range("A1:G4193").Address(ReferenceStyle:=xlR1C1) 

Set sht = Sheets.Add 

StartPvt = sht.Name & "!" & sht.Range("A1").Address(ReferenceStyle:=xlR1C1) 

Set pvtCache = ActiveWorkbook.PivotCaches.Create(_ 
    SourceType:=xlDatabase, _ 
    SourceData:=SrcData) 

Set pvt = pvtCache.CreatePivotTable(_ 
    TableDestination:=StartPvt, _ 
    TableName:="PivotTable1")

我非常感谢所有帮助。谢谢！

列数是否改变，或者只是行数？看看[这个简单的代码]（http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba）来确定最后一行.. – OldUgly

答

假设 - 您的数据透视表的动态范围是通过添加（或分心）的行数发生变化，而列数保持不变。

而不是使用ActiveSheet，尝试使用引用的对象，如Set SrcSht = Worksheets("Sheet1")，然后使用该变量。

尝试下面的代码（我的其他一些修改是在代码的注释内）。

Option Explicit 

Sub Create_Pivot_DynamicRange() 

Dim sht As Worksheet 
Dim pvtCache As PivotCache 
Dim pvt As PivotTable 
Dim StartPvt As Range 
Dim SrcData As String 
Dim pf As PivotField 

Dim SrcSht As Worksheet, LastRow As Long 

' modify "Sheet1" to your sheet's name 
Set SrcSht = Worksheets("Sheet1") 

With SrcSht 
    ' find last row with data in Column A (skip blank cells in the middle) 
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 
    ' set source data to dynamic number of rows (string) 
    SrcData = .Name & "!" & .Range("A1:G" & LastRow).Address(ReferenceStyle:=xlR1C1) 
End With 

Set sht = Sheets.Add 

' set the start position directly to a Range (there's no need to use a String as a "middle-man") 
Set StartPvt = sht.Range("A1") 

Set pvtCache = ActiveWorkbook.PivotCaches.Create(_ 
    SourceType:=xlDatabase, _ 
    SourceData:=SrcData) 

Set pvt = pvtCache.CreatePivotTable(_ 
    TableDestination:=StartPvt, _ 
    TableName:="PivotTable1") 

End Sub

答

改变你的范围到一个变量 - 如RNG 计算范围，你打算怎么做。最后一排，最后一列，最后一个单元格地址等然后使代码soething这样

Lastrow = ActiveSheet..Range("B" & Worksheets("All_Data").Rows.Count).End(xlUp).Row 
    RNG = "A1:"G" & Lastrow 
    SrcData = ActiveSheet.Name & "!" & Range(RNG).Address(ReferenceStyle:=xlR1C1)

在数据透视表中创建动态范围

相关推荐