select range for pivot table command

[Guest]

I need to select range for pivot table command.

If I select a range and then record a macro I get this for the pivot command:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"TempEC!R1C1:R229C50").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

Now I need to put a similar pivot table command into a macro program, and I
don't know how to reference the SourceData in the pivot command.

I tried using the name of a range I defined earlier in the macro, but get an
error.
I also tried using RC notation given the row and column size of the
datasource.

 

[Dave Peterson]

If you have a variable that represents the range to use:

Dim myRng as range
with worksheets("rawdataworksheetnamehere")
'I used A1 to L (lastrow with data in column A)
set myrng = .range("A1:L" & .cells(.rows.count,"A").end(xlup).row)
end with

....., Sourcedata:=myrng.address(external:=true)).createpivottable....

 

[Guest]

Using myrng.address worked great.

I think you may have had an small error in your set myrng statement, but I
used slightly different method and it worked fine.

Thanks for fast respons

 

[Dave Peterson]

I didn't test it before, but I did now. It worked the way I wanted. But maybe
not the way you wanted!

Glad you got it working, though.

Using myrng.address worked great.

I think you may have had an small error in your set myrng statement, but I
used slightly different method and it worked fine.

Thanks for fast respons