OK, I am new to coding pivot tables with VBA so please bear with me.....
I recorded a macro of myself creating a pivot table. Take a look at this line:
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"NetPrem!R1C1:R244C30").CreatePivotTable TableDestination:=Range("A1"), _
TableName:="PivotTable1"
Now, the source data thing is annoying me. I don't want to pick the data up from a fixed range. I want to pick up the data from a dynamic range.
I have tried defning my range such as:
Set rng = Sheets("NetPrem").Range([a1], [a1].End(xlToRight).End(xlDown))
or even
rng = Sheets("NetPrem").Range("A1").Currentregion
but when I try and put this in the sourcedata thing as
SourceData = rng
or
SourceData = "rng"
etc
then it doesn't like it at all.
Thanks for your help,
RET79
I recorded a macro of myself creating a pivot table. Take a look at this line:
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"NetPrem!R1C1:R244C30").CreatePivotTable TableDestination:=Range("A1"), _
TableName:="PivotTable1"
Now, the source data thing is annoying me. I don't want to pick the data up from a fixed range. I want to pick up the data from a dynamic range.
I have tried defning my range such as:
Set rng = Sheets("NetPrem").Range([a1], [a1].End(xlToRight).End(xlDown))
or even
rng = Sheets("NetPrem").Range("A1").Currentregion
but when I try and put this in the sourcedata thing as
SourceData = rng
or
SourceData = "rng"
etc
then it doesn't like it at all.
Thanks for your help,
RET79