EXCEL VBA，多个合并范围。无法将对工作表的引用从名称更改为索引

Question

在这里我不知道的东西。

我正在从新表上的多个范围内创建一个枢轴。

这有效：

arr = Array(Array("US!R6C2:R17C23", "US"), Array("Japan!R6C2:R17C23", "Japan"), Array( _
    "Other1!R6C2:R17C23", "Other1"), Array("Other2!R6C2:R17C23", "Other2"), Array( _
    "EU!R6C2:R17C23", "EU"))
ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:=arr, Version:=6).CreatePivotTable TableDestination:= _
    "", TableName:="PivotTable9", _
    DefaultVersion:=6

这不起作用：

arr = Array(Array(Worksheets(2).Range("B6:W17"), "US"), Array("Japan!R6C2:R17C23", "Japan"), Array( _
    "Other1!R6C2:R17C23", "Other1"), Array("Other2!R6C2:R17C23", "Other2"), Array( _
    "EU!R6C2:R17C23", "EU"))ere
ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:=arr, Version:=6).CreatePivotTable TableDestination:= _
    "", TableName:="PivotTable9", _
    DefaultVersion:=6

区别在于我尝试引用第一部分的方式：

Array(Worksheets(2).Range("B6:W17"), "US"))

vs

Array("US!R6C2:R17C23", "US")

任何帮助理解为什么我无法做出这一更改，而我需要做的事情将是很棒的。

Answer 1

尝试更改：

Array(Worksheets(2).Range("B6:W17"), "US"))

to：

Array(Chr(34) &Worksheets(2).Name &"!" & Worksheets(2).Range("B6:W17").Address(ReferenceStyle:=xlR1C1) & Chr(34), "US")

Answer 2

我会避免使用合并方法来创建枢纽...它有很多限制，同时存在更好的替代方案，包括：

1. 使用VBA将所有数据从单独的表融合到一个表中。请参阅https://stackoverflow.com/a/47279374/2507160
2. 使用PowerQuery做同样的事情。请参阅https://stackoverflow.com/a/47170312/2507160