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

You can use a dynamic named range for the pivot source, and use that
name in the code. For example:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"PivotRange").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

There are instructions for dynamic names he

http://www.contextures.com/xlNames01.html#Dynamic

Tim wrote:
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





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra,

Thanks a lot for your help.

Tim.

"Debra Dalgleish" wrote:

You can use a dynamic named range for the pivot source, and use that
name in the code. For example:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"PivotRange").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

There are instructions for dynamic names he

http://www.contextures.com/xlNames01.html#Dynamic

Tim wrote:
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





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html