select range for pivot table command
 

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:=xlDatab ase, 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.




--
Richard

select range for pivot table command
 

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)).createp ivottable....



Richard wrote:

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:=xlDatab ase, 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.

--
Richard

--

Dave Peterson

select range for pivot table command
 

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
--
Richard


"Dave Peterson" wrote:

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)).createp ivottable....



Richard wrote:

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:=xlDatab ase, 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.

--
Richard

--

Dave Peterson

select range for pivot table command
 

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.

Richard wrote:

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
--
Richard

"Dave Peterson" wrote:

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)).createp ivottable....



Richard wrote:

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:=xlDatab ase, 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.

--
Richard

--

Dave Peterson


--

Dave Peterson