vba - 在多个工作表中搜索一个关键字，然后创建一个数据透视表

Question

我有一个代码来创建我需要的数据透视表，但我想添加编码来搜索每个选项卡的关键字，如果找到，突出显示选项卡并创建数据透视表。在第一个选项卡中开始搜索 KeyWord = "XXXXX"，如果找到突出显示并在下面创建枢轴，然后继续到下一个选项卡，循环直到完成。如果未找到关键字，则不执行任何操作并继续下一个选项卡。

Sub Create pivot()
ActiveSheet.Select
With ActiveWorkbook.ActiveSheet.Tab
    .Color = 65535
    .TintAndShade = 0
End With
Dim shtSrc As Worksheet, shtDest As Worksheet
Dim pc As PivotCache

Set shtSrc = ActiveSheet
Set shtDest = shtSrc.Parent.Sheets.Add()
shtDest.Name = shtSrc.Name & "-Pivot"

Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:=shtSrc.Range("A1").CurrentRegion)
pc.CreatePivotTable TableDestination:=shtDest.Range("A3"), _
    TableName:="PivotTable1"
With shtDest.PivotTables("PivotTable1")
    .ColumnGrand = True
    .HasAutoFormat = True
    .DisplayErrorString = False
    .DisplayNullString = True
    .EnableDrilldown = True
    .ErrorString = ""
    .MergeLabels = False
    .NullString = ""
    .PageFieldOrder = 2
    .PageFieldWrapCount = 0
    .PreserveFormatting = True
    .RowGrand = True
    .SaveData = True
    .PrintTitles = False
    .RepeatItemsOnEachPrintedPage = True
    .TotalsAnnotation = False
    .CompactRowIndent = 1
    .InGridDropZones = False
    .DisplayFieldCaptions = True
    .DisplayMemberPropertyTooltips = False
    .DisplayContextTooltips = True
    .ShowDrillIndicators = True
    .PrintDrillIndicators = False
    .AllowMultipleFilters = False
    .SortUsingCustomLists = True
    .FieldListSortAscending = False
    .ShowValuesRow = False
    .CalculatedMembersInFilters = False
    .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotCache
    .RefreshOnFileOpen = False
    .MissingItemsLimit = xlMissingItemsDefault
    End With
   ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
   ActiveWorkbook.ShowPivotTableFieldList = True
   With ActiveSheet.PivotTables("PivotTable1").PivotFields("Suspense?")
    .Orientation = xlRowField
    .Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField 
ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("APC LC2 Amount"), "Sum of APC LC2 
Amount", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField 
ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Partner LC2 Amount"), "Sum of Partner LC2 
Amount", _
    xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField 
ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("LC2 Amount"), _
    "Sum of LC2 amount", xlSum

 End Sub

标签： vbasearchpivotworksheet