求助vba编写的透视图和透视表方法！ 的附件

rubylee

哪位大侠帮帮忙．怎么样根据此表中的sheet１的数据来画出sheet４和chart1的透视表和透视图 是用vb编辑器编写代码，为什么我的这个语句执行时会出现＂不能设置pivotitems的visible属性＂ 是我什么地方没有设置或设置的不对么？ ActiveSheet.PivotTables("数据透视表1").PivotFields(" RepCode").PivotItems(a(i)).Visible = False a(i)代表一个数组，每一个值都是column的名称． MMD7tQzt.rar (36.43 KB, 下载次数: 28)

2006-5-30 15:12 上传

点击文件名下载附件
求助vba编写的透视图和透视表方法！ 的附件

Long_III

建议楼主给个全的，代码都没有，不好调试。

这样的问题不测试，很难判断为什么不行，特别是很多对这些不熟的朋友

rubylee

Sub ³õʼ»¯Êý¾Ý()
Dim i As Long, j As Long, Hang As Long
Dim Hang1 As Long, Hang2 As Long
Dim NumPN As Long, NumM2 As Long
Dim a() As String, b() As String, c() As Long
Dim Numcolor As Long
Dim T As String
Dim Num As Long
Dim Lie As Long
Numcolor = 3: Num = 1
Worksheets(1).Name = "0"

For i = 1 To 20
If Cells(1, i) <> "" Then
If Cells(1, i) = "Line" Or Cells(1, i) = "Business_Type" Or Cells(1, i) = "Type" Or _
Cells(1, i) = "Station" Or Cells(1, i) = "Num" Then
Columns(i).Select
Selection.Delete
i = i - 1
End If
End If
Next i
For i = 65536 To 1 Step -1
If Cells(i, 1) <> "" Then
Hang = i
Exit For
End If
Next i
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"0!R1C1:R" & i & "C15").CreatePivotTable TableDestination:="", TableName:= _
"Êý¾Ý͸ÊÓ±í1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(5, 1)
ActiveSheet.Cells(5, 1).Select
ActiveSheet.PivotTables("Êý¾Ý͸ÊÓ±í1").AddFields RowFields:="RepCode", _
ColumnFields:="CorrectiveAction", PageFields:=Array("OPID", "UpStation", "TransDateTime", "QCI_Model", "Part_Num")
ActiveSheet.PivotTables("Êý¾Ý͸ÊÓ±í1").PivotFields("FailureAnalysis").Orientation = xlDataField
NumPN = ActiveSheet.PivotTables("Êý¾Ý͸ÊÓ±í1").PivotFields("RepCode").PivotItems.Count
ReDim a(NumPN - 1)
For i = 0 To NumPN - 1
'a(i) = ActiveSheet.PivotTables("Êý¾Ý͸ÊÓ±í1").PivotFields(" RepCode").PivotItems(i + 1).Name
a(i) = ActiveSheet.PivotTables("Êý¾Ý͸ÊÓ±í1").PivotFields("RepCode").PivotItems(i + 1).Name
If a(i) = "BATTERY" Or a(i) = "CPU" Or a(i) = "CRT CABLE" Or a(i) = "Digitizer Panel" _
Or a(i) = "Digitizer Pen" Or a(i) = "HDD" Or a(i) = "I/V" Or a(i) = "LCD" _
Or a(i) = "LCD Cable" Or a(i) = "ODD" Or a(i) = "POWER CABLE" Or a(i) = "WL/B" _
Or a(i) = "RAM" Then
'ActiveSheet.PivotTables("Êý¾Ý͸ÊÓ±í1").PivotFields(" RepCode").PivotItems(a(i)).Visible = True
ActiveSheet.PivotTables("Êý¾Ý͸ÊÓ±í1").PivotFields("RepCode").PivotItems(a(i)).Visible = True
Else
'ActiveSheet.PivotTables("Êý¾Ý͸ÊÓ±í1").PivotFields(" RepCode").PivotItems(a(i)).Visible = False
ActiveSheet.PivotTables("Êý¾Ý͸ÊÓ±í1").PivotFields("RepCode").PivotItems(a(i)).Visible = False
End If
Next i
NumM2 = ActiveSheet.PivotTables("Êý¾Ý͸ÊÓ±í1").PivotFields(" CorrectiveAction").PivotItems.Count
ReDim b(NumM2 - 1)
For i = 0 To NumM2 - 1
b(i) = ActiveSheet.PivotTables("Êý¾Ý͸ÊÓ±í1").PivotFields(" CorrectiveAction").PivotItems(i + 1).Name
If b(i) = "REANW" Or b(i) = "REAW" Or b(i) = "REDL" Or b(i) = "RWK" Or b(i) = "SWAP" Then
ActiveSheet.PivotTables("Êý¾Ý͸ÊÓ±í1").PivotFields(" CorrectiveAction").PivotItems(b(i)).Visible = True
Else
ActiveSheet.PivotTables("Êý¾Ý͸ÊÓ±í1").PivotFields(" CorrectiveAction").PivotItems(b(i)).Visible = False
End If
Next i

end sub

我只会写这么多了，还不知道对不对．乱码部分是＂数据透视表１＂

这个是我写在一个新建的ＥＸＣＥＬ中的，把附件中的数据拷到这个新建的ＥＸＣＥＬ中后，全选数据，运行此过程．

rubylee

帮帮忙啊！感激不尽

rubylee

没有人会么？大家帮帮忙了！