I saved a macro that builds a given pivot table in excel and is shown below. Question is how do I change this so that the "SourceData" range can be given in row number, column number format rather than what's shown

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
"Sheet1!R1C1:R29C20").CreatePivotTable TableDestination:="", TableName:=
"PivotTable1
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1
ActiveSheet.Cells(3, 1).Selec
With ActiveSheet.PivotTables("PivotTable1"
.ColumnGrand = Fals
.RowGrand = Fals
.SmallGrid = Fals
End Wit
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("WMW", "WML"
, "COIL HGT"), ColumnFields:="Run#
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Qty").Orientation =
xlDataFiel
Range("B7").Selec
Selection.Delet
Range("A7").Selec
Selection.Delet

I just set it to my range:

dim myRng as range
with worksheets("sheet1")
set myRng = .range("a1:T" & .cells(.rows.count,"A").end(xlup).row)
end with

later....

....sourcedata:=myrng.address(external:=true).....



Darrell Wesley wrote:

I saved a macro that builds a given pivot table in excel and is shown below. Question is how do I change this so that the "SourceData" range can be given in row number, column number format rather than what's shown.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!R1C1:R29C20").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.RowGrand = False
.SmallGrid = False
End With
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("WMW", "WML" _
, "COIL HGT"), ColumnFields:="Run#"
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Qty").Orientation = _
xlDataField
Range("B7").Select
Selection.Delete
Range("A7").Select
Selection.Delete

--

Dave Peterson