|
表格里有4个sheet,分别命名为"1" "2" "3" "4",需要对表格进行数据插入操作,四个sheet进行同样的数据插入操作。要求代码先对名为"1"的sheet插入透视表操作,然后是"2" "3" "4",代码运行时始终在名为"1"的sheet里运行,直至出错。代码如下,请高手帮忙看看是那里出了问题?
Sub TEST11()
'
' TEST Macro
'
'
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Dim I As Integer
Dim S As String
For Each WSD In Worksheets '在表格内循环所有sheet
'匹配sheet名字
For I = 2 To 7
S = CStr(I)
If WSD.Name = "S" Then
Set WSD = Worksheets("S")
End If
Next
Range("AQ:CA").EntireColumn.Clear '清除其他数据透视表
' 定义输入数据以及数据源
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)
' 创建数据透视表
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, FinalCol + 6))
With PT.PivotFields("PLINE")
.Orientation = xlColumnField
.Position = 1 'PLINE作为列标签放在第一列
End With
With PT.PivotFields("PMODLE")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
.NumberFormat = "#,##0"
End With
With PT.PivotFields("是否过保")
.Orientation = xlRowField
.Position = 1 '是否过保作为行标签,放在第一行
.PivotItems("否").Visible = False
.PivotItems("#NUM!").Visible = False
.PivotItems("#VALUE!").Visible = False
End With
With PT.PivotFields("PTYPE")
.Orientation = xlRowField
.Position = 2
End With
With PT.PivotFields("PMODLE")
.Orientation = xlRowField
.Position = 3
End With
Range("BA2").Select '选择起始坐标;
Next
End Sub
|
|