Создание динамического диапазона в сводной таблице

Я пытаюсь изменить свой фиксированный диапазон (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")

Я очень ценю любую помощь. Спасибо!

2 ответа

  1. Измените диапазон на переменный RNG
    Рассчитайте диапазон, как вы планируете это сделать. Последняя строка, последний столбец, Последний адрес ячейки и т. д
    Тогда сделайте код таким

        Lastrow = ActiveSheet..Range("B" & Worksheets("All_Data").Rows.Count).End(xlUp).Row
        RNG = "A1:"G" & Lastrow
        SrcData = ActiveSheet.Name & "!" & Range(RNG).Address(ReferenceStyle:=xlR1C1)
    
  2. Предположение-динамический диапазон сводной таблицы изменяется на количество добавленных строк (или отвлеченных), в то время как количество столбцов остается постоянным.

    Вместо использования 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