运行时错误5 - 无效的过程调用或参数 [英] Run Time Error 5 - Invalid Procedure Call or Argument

问题描述

我需要这个宏的帮助。每次运行它，我都会收到以下错误。我认为这是一个简单的宏，我可以让任何人使用我的团队，使他们花更少的时间比他们在每次运行报表时手动创建这个数据透视表。但是，它不工作。请看下面的错误并提供建议。我夸张并斜体错误。

I need help with this macro. Every time I run it, I get the error below. I thought it was a simple macro that I could have anybody on my team use to make it take a less time than they were taking to manually create this PivotTable every time they ran the report. However, it's not working. Please see error below and advise. I emboldened and italicized the error.

Sub LEDOTTR()
'
' LEDOTTR Macro
'

'
    Range("A87").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ***ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R87C1:R8214C25", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="LED OTTR!R1C1", TableName:="PivotTable6", _
        DefaultVersion:=xlPivotTableVersion14***
    Sheets("LED OTTR").Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("LED")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Hierarchy name")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable6").PivotFields("LED").CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("LED")
        .PivotItems("LED Marine").Visible = False
        .PivotItems("LL48 Linear LED").Visible = False
        .PivotItems("Other").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable6").PivotFields("LED"). _
        EnableMultiplePageItems = True
    ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
        "PivotTable6").PivotFields("   Late " & Chr(10) & "Indicator"), "Sum of    Late " & Chr(10) & "Indicator", _
        xlSum
    ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
        "PivotTable6").PivotFields("Early /Ontime" & Chr(10) & "   Indicator"), _
        "Sum of Early /Ontime" & Chr(10) & "   Indicator", xlSum
End Sub

推荐答案

您的问题的答案是位于这里。

TableDestination：=LED OTTR！R1C1 需要用单引号包围，以使其工作 TableDestination：='LED OTTR'！R1C1

Your sheet name in TableDestination:="LED OTTR!R1C1" needs to be surrounded with single quotes in order for it to work TableDestination:="'LED OTTR'!R1C1"

如果您在重新运行代码之前不删除此数据透视表，那么您也会遇到重复名称的问题。

You will also have problems with the duplicated name if you do not delete this PivotTable before rerunning the code.

这篇关于运行时错误5 - 无效的过程调用或参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！