pivot table range selection

[Peruanos72]

Hello all,

I have the following code and I need code that will select the correct data
range as
the range will change from day to day. Lines 2-5 is what I have to start with.

Thoughts?

Range("A4").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"bluecard_homeplanaid!R3C1:R9067C8").CreatePivotTable
TableDestination:= _
"'[bluecard_homeplanaid_Total-Charges_Master.xls]pivot_table'!R1C1",
TableName _
:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("PivotTable1").PivotFields("group id")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Claim #")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Total Charge"), "Sum of Total Charge",
xlSum
Columns("C:C").Select
Selection.NumberFormat = "#,##0.00"
Range("D1").Select

 

[Patrick Molloy]

try

Selection.CurrentRegion.Select

 

[Peruanos72]

Thanks Patrick. That did select the proper range but I needed to change line
5 in the code below as well. Even with your suggestion the pivot table is
still using the range that's hard coded.

I just changed the range to include more rows of data and then removed the
"Blanks" in the pivot table. That seems to work for now.

Thanks again for your help.

try

Selection.CurrentRegion.Select

Hello all,

I have the following code and I need code that will select the correct data
range as
the range will change from day to day. Lines 2-5 is what I have to start with.

Thoughts?

Range("A4").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"bluecard_homeplanaid!R3C1:R9067C8").CreatePivotTable
TableDestination:= _
"'[bluecard_homeplanaid_Total-Charges_Master.xls]pivot_table'!R1C1",
TableName _
:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("PivotTable1").PivotFields("group id")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Claim #")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Total Charge"), "Sum of Total Charge",
xlSum
Columns("C:C").Select
Selection.NumberFormat = "#,##0.00"
Range("D1").Select