Hi folks,

The following is my macro to create pivot table. It works fine but I need
some help on the row range. For example, my new data source table have 100
rows and 3 columes. When I run the following macro, I will have wrong pivot
table because the range is "Sheet1!R1C1:R4C2". Could anyone tell me how to
change the range ("Sheet1!R1C1:R100C3") by itself?

Any help will be deeply appreciated.

Thanks in advance.

Tim.

Sub Macro1()
'
' Macro1 Macro
'

'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!R1C1:R4C2").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="State"

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Invoice").Orientation = _
xlDataField
End Sub