I am new to working with pivot table /macro
I have a pivot table. The total columns are always changing.

How do I declare a variable in the data source to find the last used column?
Hard coding it will not help me.

Data can range from A:BD or A:AR. It all depends.

Thanks a lot.

Below is a copy of my code.

CELLS.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'full'!A:BD").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.CELLS(3, 1)
ActiveSheet.CELLS(3, 1).Select
With ActiveSheet.PivotTables("PivotTable4")
.ColumnGrand = False
.RowGrand = False
End With

You could use the current region for cell A1:

'=============================
Dim wsData As Worksheet
Set wsData = Worksheets("Full")
Dim rngData As Range
Set rngData = wsData.Range("A1").CurrentRegion

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

mju wrote:
I am new to working with pivot table /macro
I have a pivot table. The total columns are always changing.

How do I declare a variable in the data source to find the last used column?
Hard coding it will not help me.

Data can range from A:BD or A:AR. It all depends.

Thanks a lot.

Below is a copy of my code.

CELLS.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'full'!A:BD").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.CELLS(3, 1)
ActiveSheet.CELLS(3, 1).Select
With ActiveSheet.PivotTables("PivotTable4")
.ColumnGrand = False
.RowGrand = False
End With



--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Thanks. tried it but i got a run time error 1004
Pivot table feild name is not valid.
"Debra Dalgleish" wrote:

You could use the current region for cell A1:

'=============================
Dim wsData As Worksheet
Set wsData = Worksheets("Full")
Dim rngData As Range
Set rngData = wsData.Range("A1").CurrentRegion

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

mju wrote:
I am new to working with pivot table /macro
I have a pivot table. The total columns are always changing.

How do I declare a variable in the data source to find the last used column?
Hard coding it will not help me.

Data can range from A:BD or A:AR. It all depends.

Thanks a lot.

Below is a copy of my code.

CELLS.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'full'!A:BD").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.CELLS(3, 1)
ActiveSheet.CELLS(3, 1).Select
With ActiveSheet.PivotTables("PivotTable4")
.ColumnGrand = False
.RowGrand = False
End With



--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Select cell A2 on the data sheet
On the keyboard, press Ctrl+Shift+8
That should select the current region.

What is selected? Is there a column included that doesn't have a heading
in row 1?


mju wrote:
Thanks. tried it but i got a run time error 1004
Pivot table feild name is not valid.
"Debra Dalgleish" wrote:


You could use the current region for cell A1:

'=============================
Dim wsData As Worksheet
Set wsData = Worksheets("Full")
Dim rngData As Range
Set rngData = wsData.Range("A1").CurrentRegion

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

mju wrote:

I am new to working with pivot table /macro
I have a pivot table. The total columns are always changing.

How do I declare a variable in the data source to find the last used column?
Hard coding it will not help me.

Data can range from A:BD or A:AR. It all depends.

Thanks a lot.

Below is a copy of my code.

CELLS.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'full'!A:BD").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.CELLS(3, 1)
ActiveSheet.CELLS(3, 1).Select
With ActiveSheet.PivotTables("PivotTable4")
.ColumnGrand = False
.RowGrand = False
End With



--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com





--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com