pivot table range selection

  • Thread starter Thread starter Peruanos72
  • Start date Start date
P

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

Patrick Molloy said:
try

Selection.CurrentRegion.Select

Peruanos72 said:
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
Click to expand...
Click to expand...
 
Back
Top