当前位置: 动力学知识库 > 问答 > 编程问答 >

Excel VBA: Making Pivot Table from list of Sheet names

问题描述:

This is the start of a macro that successfully creates a pivot table based on cells from the specified sheets:

aray = Array("'Sheet1'!R1C5:R102C10", "'Sheet2'!R1C5:R102C10", "'Sheet3'!R1C5:R102C10", "'Sheet4'!R1C5:R102C10")

ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= aray, _

Version:= _

xlPivotTableVersion15).CreatePivotTable TableDestination:= _

"'[Filename.xlsm]sheet5'!R1C1", TableName:= _

"RatingSumPiv", DefaultVersion:=xlPivotTableVersion15

My problem is that I don't know what the sheets are named or how many there are. Their names will be in a range (a ListObject) named "UnitNumbers".

How can I automatically load the sheet names into the Array?

网友答案:
Sub Sample()
Dim loSheetNames As ListObject
Dim rngCurrentSheetName As Range
Dim toprowoflo As Long
Dim lngCurrentIndex As Long

Set loSheetNames = ActiveWorkbook.Worksheets("Sheet1").ListObjects("listob1")
toprowoflo = loSheetNames.Range.Row
ReDim aray(loSheetNames.Rows.Count)

For Each rngCurrentSheetName In loSheetNames.Range
    lngCurrentIndex = rngCurrentSheetName.Row - toprowoflo + 1
    aray(lngCurrentIndex) = rngCurrentSheetName & "!R1C5:R102C10"
Next rngCurrentSheetName

ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:=aray, _
    Version:= _
    xlPivotTableVersion15).CreatePivotTable TableDestination:= _
    "'[Filename.xlsm]sheet5'!R1C1", TableName:= _
    "RatingSumPiv", DefaultVersion:=xlPivotTableVersion15

End Sub
分享给朋友:
您可能感兴趣的文章:
随机阅读: