help with Pivot table

[GMet]

I have copied 2 different pivot table macros to adapt to my data. I get the
same error with both.
Here is the code:

worksheets.add
ActivSheet.PivotTableWizard sourcetype:=xldatabase, _
SourceData:=Sheets("Rollup").UsedRange, _
Tabledestination:=ActiveCell, Tablename:= _
"BOEpivot", savedata:=False
With ActiveSheet.PivotTables ("BOEpivot")
.AddFields RowFields:="WBS Desc", _
columnFields:="Events", PageFields:="IPT"
.PivotFields(WBS Desc").Orientation = xlDataField
.PivotFields("Budget").function = xlsum
end with


The error is:

The Pivottable field name is not valid. To create a pivottable report, you
must use data that is organized as a list with labeled columns. If you are
changing the name of a Pivottable field, you must type a new name for the
field.

The field names are exactly as in the source data (of about 30 fields).

This is the first time using Pivot tables and I am confused.

GMet

 

[Tom Ogilvy]

this error refers to the source data. One of the columns in your source
data doesn't have a header, has a duplicate header or otherwise has an
invalid field name. I see you are using UserRange to define your data.
That could include blank columns (and/or blank rows). I suspect in this
case it includes blank columns.


Instead of Used Range,
Sheets("Rollup").UsedRange

try:
Sheets("Rollup").Range("A1").CurrentRegion

This assumes that if you selected A1 in Sheet Rollup and did Ctrl+Shift+8
that you would select all your data and nothing extra.