数据透视表中的Excel VBA宏 [英] Excel VBA Macro from Pivot table
问题描述
我正在尝试从具有数据透视表的Excel数据透视表生成少量报告,并尝试编写VBA代码以开发宏,以便它自动为需要将报告发送至的25个分支生成报告.可能使用宏,我也可以使电子邮件自动化.
I am trying to generate few reports from Excel Pivot Table where I have Pivot Table and trying to write a VBA code to develop Macro so that it automatically generate the report for 25 Branches where I need to send the report to. Possibly using Macro I can automate the email too.
任何人都可以从哪里开始?
Can any one help where to start from?
我从数据透视表中获取了以下代码
I got following code from Pivot table
Sub printit()
Dim pt As PivotTable, pi As PivotItem, pf As PivotField
Dim lLoop As Long
Set pt = Sheet1.PivotTables(1)
Set pf = pt.PageFields(1)
For Each pi In pf.PivotItems
Sheet1.PivotTables(1).PageFields(1).CurrentPage = pi.Value
Sheet1.PrintOut
lLoop = lLoop + 1
Next pi
End Sub
根据工作表我已更改为关注
Which I have changed to following according to my worksheet
Sub PrintAllPivotFilters()
Dim pt As PivotTable, pi As PivotItem, pf As PivotField
Dim lLoop As Long
Set pt = Sheet3.Certifications
Set pf = pt.Branch
For Each pi In pf.PivotItems
Sheet1.Certifications.Branch.CurrentPage = pi.Value
Sheet1.PrintOut
lLoop = lLoop + 1
Next pi
End Sub
推荐答案
您可以使用数据透视表的showPages方法
You can use showPages method of pivottable
为页面字段中的每个项目创建一个新的数据透视表报表.每个 在新的工作表上创建新的报告.
Creates a new PivotTable report for each item in the page field. Each new report is created on a new worksheet.
语法表达式. ShowPages(PageField)
Syntax expression . ShowPages( PageField )
expression一个表示数据透视表对象的变量.
expression A variable that represents a PivotTable object.
[pageField的可选参数.]
[Optional parameter of pageField.]
代码:
Code:
ThisWorkbook.Worksheets("Sheet1").PivotTables(1).ShowPages "name of pagefield"
有关完整示例,请参见此处或
For a fuller example see here or here.
然后,您可以使用简单的代码遍历这些工作表,并将每张工作表作为电子邮件附件发送.请参阅以下内容,以开始使用然后通过电子邮件发送 使用Outlook(Windows)从Excel发送邮件.
You can then use simple code to loop over these sheets and send each sheet as an e-mail attachment. See the following for getting you started with then e-mailing Mail from Excel with Outlook (Windows).
请记住,您可以使用现有代码轻松地引用每个新创建的表格以进行导出
Remember you can easily reference each newly created sheet for export with your existing code
For Each pi In pf.PivotItems
Worksheets(pi.Value) '<==pass this to sub that e-mails
Next pi
这篇关于数据透视表中的Excel VBA宏的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!