vba excel按缓存或新建创建数据透视表-Java 学习之路

在vba excel宏工具上，我需要从两个数据表创建许多新的工作表和数据透视表 .

我们有两种方式

使用pivot cache来创建新的数据透视表 .
一次又一次地创建具有相同数据表的新数据透视表 .

如果我选择第二种方法会有什么影响，

这会让我们的excel文件更重吗？

想要跳过第一种方法，因为如果需要依赖，第一个数据透视表应该存在于每个excel工作簿中？

使用excel 2010 .

1 回答

我做了一个项目，它将在现有工作表中创建数据透视表，并将数据复制到新的电子表格 . 如果这是你的问题..请参考下面的代码

Dim objtable as pivottable, objfield as pivotfield
set objtable = sheet1.pivotwizard
   ptname = activesheet.pivottables(1).name 'this code is to give pivot table name which will help in creating new pivot tables
   set objfield.objtable.pivotfields("Month") 'this code will select month in page field
 objfield.orientation = xlpagefield
set objfield = objtable.pivotfields ("Dept & Mgr") 'this code will select dept & mgr in page field
objfield.orientation = xlpagefield
set objfield = objtable.pivotfields("Legal Entity") 'this code will select legal entity in row field
objfield.orientation = xlrowfield
set objfield = objtable.pivotfields("Cost Code") 'this code will select cost code in row field
objfield.orientation = xlrowfield
set objfield = objtable.pivotfields("CC Description") 'this code will select CC Description in row field
objfield.orientation = xlrowfield
set objfield = objtable.pivotfields("User") 'this code will select Usr in row field
objfield.orientation = xlrowfield
set objfield = objtable.pivotfields("Carrier") 'this code will select carrier in row field
objfield.orientation = xlrowfield
set objfield = objtable.pivotfields("Description") 'this code will select Description in row field
objfield.orientation = xlrowfield
set objfield = objtable.pivotfields("Amount Incl Tax") 'this code will amount incl tax in Datafield
objfield.orientation = xlDatafield
activesheet.pivottables(ptname).format xlreport6 '

回复于 2024-04-20T21:53:49+08:00

vba excel按缓存或新建创建数据透视表

1 回答

相关问题