Convert Range of a Pivot Table to R1C1 format

I'm trying to specify the range of a Pivot Table as the .RowSource of a Userform Listbox. It needs to be represented in R1C1 format. Can anyone tell me how to do that? (Note: the TableRange1 is the range of cells that I really want as input to the listbox)

Here is the code I have so far. (The Pivot table is selected ok but the .RowSource doesn't work)

Dim RngOfPvt As String

Worksheets("Temp Pivots").PivotTables("PivotTable1").TableRange1.Select
MsgBox Application.ConvertFormula(Worksheets("Temp Pivots").PivotTables("PivotTable1").SourceData, xlR1C1, xlA1)

RngOfPvt = Application.ConvertFormula(Worksheets("Temp Pivots").PivotTables("PivotTable1").SourceData, xlR1C1, xlA1)

UserForm1.ListBox1.RowSource = RngOfPvt
UserForm1.Show

Thanks for your help.

Originally Posted by JosephP

Please Login or Register  to view this content.

not sure why your code used sourcedata or why you wanted r1c1 references?

Thanks for the response Joseph. When I try your line of code I get the error "Permission denied". Any thoughts?

Joseph,

After I clear the rowsource property I get the error "Could not set the Value property. Invalid property value. Here is the line of code.

UserForm1.ListBox1.List = Worksheets("Temp Pivots").PivotTables("PivotTable1").TableRange1.Value

Thoughts?

Ok. I got it to work. Apparently it was a problem with clearing the listbox using the .clear method.
I commented out the clear and entered the line after it and it worked fine.

Please Login or Register  to view this content.

For the benefit of others, what this code does is put the TableRange1 (In my case headers and data) of the pivot table into the listbox.

Thanks Joseph for your help.

Gorilla

You are good!

Thanks.