SourceData range in pivottables

[chaudi]

Hi,

Does anyone know how to define a range for the source within a
pivottable. eg

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
XXXXXXXXXX, TableDestination:="", TableName:="PivotTable4"
ActiveSheet.PivotTables("PivotTable4").AddFields
RowFields:="Level1"

ActiveSheet.PivotTables("PivotTable4").PivotFields("Duration").Orientation
= _
xlDataField


I would like to replace the XXXXXX with the UsedRange within a
worksheet.

Thanks

 

[Dave Peterson]

UsedRange can sometimes be bigger than you think.

I'm not sure I'd trust it. But I bet you can pick out a column that always has
data and you know how many columns your data has.

I'm assuming 13 columns and column D is the one with data in it all the time:

dim myRng as range
with worksheets("sheet1")
set myrng = .range("A1:M" & .cells(.rows.count,"D").end(xlup).row)
end with

Then you're pivottable code could look like:
....., sourcedata:=myrng.address(external:=true), ....