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?

(Cross-posted at