Pivot table Macro help!!!

M

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
 
D

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
'=============================
 
D

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top