Sub PTTest()
Dim SH As Worksheet ' the current worksheet from the colection of workbooks
Dim PT As PivotTable ' the current pivot table from the current worksheet
Dim PTC As Range ' the cell range of the current pivot table
Dim Tmp As String ' the buffer for concatenated cell values
Tmp = ""
' process all sheets, as Pivot table objects are contained by sheets
For Each SH In ActiveWorkbook.Worksheets
For Each PT In SH.PivotTables
For Each PTC In PT.TableRange1.Cells
' all cells in one buffer, seperated by ";"
' if you want to include page header cells, use
' "PT.TableRange2.Cells" instead
Tmp = Tmp & PTC & ";"
Next PTC
' *** do something *** with the buffer
' ok very simple we print it into the debugger's Immediate window
Debug.Print Tmp
' empty buffer for next pivot table
Tmp = ""
Next PT
Next SH
End Sub
1 回答
快速而肮脏的一个让你前进;一个线性字符串中的数据透视表的所有单元格,由";"分隔 . 这应该为使用哪些方法和属性提供足够的灵感 . 注意:
Tmp
无法保存无限大的数据透视表,如果它们变得非常大,请考虑将Tmp
写入文件 .希望有所帮助....祝你好运MikeD