vba 创建数据透视表时动态选择所有数据

Dynamically select all data when creating a pivot table

提问by Luke101

I have created a VBA macro to create a pivot table on the fly. The source data changes everyday, so there are different number of records each day. In the macro the number of records to select is hard coded. Is there a way to select all the data in the source table each day - instead of a predefined number of records

我创建了一个 VBA 宏来动态创建数据透视表。源数据每天都在变化，所以每天有不同数量的记录。在宏中，要选择的记录数是硬编码的。有没有办法每天选择源表中的所有数据 - 而不是预定义数量的记录

Application.DisplayAlerts = False
Call DeleteAllPivotTablesInWorkbook
Sheets("ALL").Select
If CheckSheet("AllSummary") Then
    Sheets("AllSummary").Delete
End If
Sheets.Add.Name = "AllSummary"
Application.DisplayAlerts = True
Sheets("AllSummary").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "All!R1C1:R26885C47", Version:=xlPivotTableVersion10).CreatePivotTable _
    TableDestination:="AllSummary!R3C1", TableName:="PivotTable3", DefaultVersion _
    :=xlPivotTableVersion10
Sheets("AllSummary").Select
Cells(3, 1).Select

As you can see the sourcdata value is hard coded. How can I select all the data regardless of the number of records?

如您所见， sourcedata 值是硬编码的。无论记录数如何，如何选择所有数据？

回答by JosieP

you can also use

你也可以使用

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "All!" & Sheets("All").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1), Version:=xlPivotTableVersion10).CreatePivotTable _
    TableDestination:="AllSummary!R3C1", TableName:="PivotTable3", DefaultVersion _
    :=xlPivotTableVersion10

回答by Stepan1010

Or if you keep your data on a separate sheet than your pivot table destination you can just use a last row/column/cell type selection to select all the relevant data on your data sheet:

或者，如果您将数据保存在与数据透视表目的地不同的工作表上，您只需使用最后一行/列/单元格类型选择来选择数据表上的所有相关数据：

Sub Excel2010Syntax()
    Sheets("Sheet1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R16C4", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet2!R20C1", TableName:="PivotTable4", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet2").Select
    Cells(1, 1).Select
End Sub

This was recorded in excel 2010 ( I think you have an earlier version ) but you get the idea. Just have a dedicated data sheet and then select all the data on it to create your pivot table.

这是在 excel 2010 中记录的（我认为你有一个早期版本）但你明白了。只需有一个专用的数据表，然后选择其上的所有数据即可创建数据透视表。

EDIT:

编辑：

Or just run this macro:

或者直接运行这个宏：

Sub Answer()
Dim wb As Workbook
Dim ws As Worksheet
Dim rng1 As Range
Set wb = ActiveWorkbook
Set ws = wb.Sheets("All")
Set rng1 = ws.Cells.Find("*", ws.[a1], xlFormulas, , , xlPrevious)
Sheets("AllSummary").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "All!R1C1:R" & CStr(rng1.Row) & "C" & CStr(rng1.Column), Version:=xlPivotTableVersion10).CreatePivotTable _
    TableDestination:="AllSummary!R3C1", TableName:="PivotTable3", DefaultVersion _
    :=xlPivotTableVersion10
Sheets("AllSummary").Select
Cells(3, 1).Select
End Sub

回答by Dick Kusleika

First, consider if you should be recreating the pivot table. If you just want to change where the pivot table points to, you can do that without deleting and creating it.

首先，考虑是否应该重新创建数据透视表。如果您只想更改数据透视表指向的位置，则无需删除和创建即可。

This sub finds the first pivot table on the AllSummary sheet and changes its PivotCache's SourceData property. The SourceData property is changed using the CurrentRegion property of the range object. When you're dealing with pivot tables, you usually have pretty well structured data and if that's the case, CurrentRegion will return the range that includes any new rows or columns added.

此 sub 查找 AllSummary 表上的第一个数据透视表并更改其 PivotCache 的 SourceData 属性。使用范围对象的 CurrentRegion 属性更改 SourceData 属性。当您处理数据透视表时，您通常拥有结构良好的数据，如果是这种情况，CurrentRegion 将返回包含添加的任何新行或列的范围。

The Address property uses R1C1 because that's what SourceData expects and also uses True in the External argument to get the sheet's name in the address.

Address 属性使用 R1C1，因为这是 SourceData 所期望的，并且还在 External 参数中使用 True 来获取地址中的工作表名称。

Sub AdjustPtSource()

    Dim pt As PivotTable

    With ActiveWorkbook
        Set pt = .Worksheets("AllSummary").PivotTables(1)
        pt.PivotCache.SourceData = .Worksheets("All").Range("A1").CurrentRegion.Address(, , xlR1C1, True)
    End With

End Sub

If you must delete and recreate, you can still use CurrentRegion to get the whole contiguous range.

如果您必须删除并重新创建，您仍然可以使用 CurrentRegion 来获取整个连续范围。

Sub MakePT()

    Dim shSum As Worksheet
    Dim shAll As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable

    Const sWSSUMMARY As String = "AllSummary"

    DeleteAllPivotTablesInWorkbook
    Set shAll = ActiveWorkbook.Worksheets("All")

    If CheckSheet(sWSSUMMARY) Then
        Sheets(sWSSUMMARY).Delete
    End If

    Set shSum = ThisWorkbook.Worksheets.Add
    shSum.Name = sWSSUMMARY

    Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, shAll.Range("A1").CurrentRegion)
    Set pt = pc.CreatePivotTable(shSum.Cells(3, 1))

End Sub