如何利用Excel VBA基于单元格下拉列表生成筛选后的多年份成本透视表？

Hey there! No need to apologize—VBA can feel like a maze when you're not working with it daily. Let's fix up your code to add that Area filter from your dropdown and get you the pivot table you need.

Key Changes We'll Make #

• Grab the selected Area from your Sheet1 A1 dropdown
• Add an Area filter to your pivot table and set it to the selected value
• Add a quick check to make sure the user actually selected an Area first

Modified Full Code #

Sub GenerateFilteredPivot()
    ' Get the selected area from the dropdown in Sheet1 A1
    Dim selectedArea As String
    selectedArea = Sheet1.Range("A1").Value
    
    ' Validate selection to avoid empty pivot tables
    If selectedArea = "" Then
        MsgBox "Please pick an Area from the dropdown first!", vbExclamation
        Exit Sub
    End If
    
    ' Reference your prepped Outlet table
    Application.Goto Reference:="Table5"
    
    ' Create new worksheet for the pivot table
    Dim wsNew As Worksheet
    Set wsNew = Sheets.Add
    
    ' Build pivot cache and table from your existing connection
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("WorksheetConnection_Book1!Table5"), Version:=6). _
        CreatePivotTable TableDestination:=wsNew.Name & "!R3C1", TableName:="PivotTableCost" _
        , DefaultVersion:=6
    
    ' Keep your original pivot table formatting settings
    With ActiveSheet.PivotTables("PivotTableCost")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = True
        .CompactRowIndent = 1
        .VisualTotals = False
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = True
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .DisplayEmptyRow = False
        .DisplayEmptyColumn = False
        .AllowMultipleFilters = True
        .SortUsingCustomLists = True
        .DisplayImmediateItems = True
        .ViewCalculatedMembers = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = True
        .RowAxisLayout xlCompactRow
    End With
    
    ActiveSheet.PivotTables("PivotTableCost").PivotCache.RefreshOnFileOpen = False
    ActiveSheet.PivotTables("PivotTableCost").RepeatAllLabels xlRepeatLabels
    
    ' Add Area as a filter field and apply the selected value
    With ActiveSheet.PivotTables("PivotTableCost").CubeFields("[Table5].[Area]")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTableCost").PivotFields("[Table5].[Area]").CurrentPage = selectedArea
    
    ' Add Outlet as the row field (your original setup)
    With ActiveSheet.PivotTables("PivotTableCost").CubeFields("[Table5].[Outlet]")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    ' Add all your cost and percentage fields (keeping your original code)
    ActiveSheet.PivotTables("PivotTableCost").AddDataField ActiveSheet.PivotTables( _
        "PivotTableCost").CubeFields("[Measures].[Sum of 2020 Cost]"), "Sum of 2020 Cost"
    ActiveSheet.PivotTables("PivotTableCost").AddDataField ActiveSheet.PivotTables( _
        "PivotTableCost").CubeFields("[Measures].[Sum of 2019 Cost]"), "Sum of 2019 Cost"
    ActiveSheet.PivotTables("PivotTableCost").AddDataField ActiveSheet.PivotTables( _
        "PivotTableCost").CubeFields("[Measures].[Sum of 2018 Cost]"), "Sum of 2018 Cost"
    ActiveSheet.PivotTables("PivotTableCost").AddDataField ActiveSheet.PivotTables( _
        "PivotTableCost").CubeFields("[Measures].[Sum of 2017 Cost]"), "Sum of 2017 Cost"
    ActiveSheet.PivotTables("PivotTableCost").AddDataField ActiveSheet.PivotTables( _
        "PivotTableCost").CubeFields("[Measures].[Sum of 2020%]"), "Sum of 2020%"
    ActiveSheet.PivotTables("PivotTableCost").AddDataField ActiveSheet.PivotTables( _
        "PivotTableCost").CubeFields("[Measures].[Sum of 2019%]"), "Sum of 2019%"
    ActiveSheet.PivotTables("PivotTableCost").AddDataField ActiveSheet.PivotTables( _
        "PivotTableCost").CubeFields("[Measures].[Sum of 2018%]"), "Sum of 2018%"
    ActiveSheet.PivotTables("PivotTableCost").AddDataField ActiveSheet.PivotTables( _
        "PivotTableCost").CubeFields("[Measures].[Sum of 2017%]"), "Sum of 2017%"
End Sub

Quick Notes #

• Double-check that Table5 includes the Area column—this is critical for filtering. If it doesn't, update your source data to include Area values alongside Outlet and cost metrics.
• If your CubeField names don't match (e.g., your Area field has a different name in Table5), tweak [Table5].[Area] to match your actual field name.
• The validation check prevents empty pivot tables by prompting users to select an Area first if they click the button without making a choice.

Give this a spin—it should generate a pivot table filtered exactly to the Area you pick from the dropdown. If you hit any snags with field names or connections, just adjust those parts to match your workbook setup!

内容的提问来源于stack exchange，提问作者pod12345