Pivot table Macro help!!!

[mju]

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:=xlDatabase, 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]

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:=xlDatabase, _
SourceData:=rngData).CreatePivotTable _
TableDestination:="", TableName:="PivotTable4", _
DefaultVersion:=xlPivotTableVersion10
'=============================

 

[mju]

Thanks. tried it but i got a run time error 1004
Pivot table feild name is not valid.

 

[Debra Dalgleish]

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?