VBA to refer to a Crosstab

[former_member725526]

Hi

I'm looking to refer to a crosstable as a ListObject within VBA, however I cannot figure it out how to.

Power Query does recognize BEx crosstables as table, therefore I was thinking I could do the same with VBA.

I'm trying to do the following, however it runs into an error, saying subscript out of range, meaning it cannot be found. Any ideas?

dim tbl as ListObject

set tbl = mysheet.ListObject("SAPCrosstable1")

Thanks!

[abastic · Accepted solution]

Try this

Dim DirArray As Variant
DirArray = mysheet.Range("SAPCrosstable1").Value

[former_member725526 · Accepted solution]

Thanks Alen!

The reason I was looking to do this, because I wanted to copy the dataset (without headers) from the Crosstable(s) without having to check the last row / last column. I guess it would be still faster to copy/paste the dataset with the Range.Copy / PasteSpecial method, rather than looping through the whole array.

Regardless I've learned something new!