automatic pivot tables

K

kilter

I am trying to automate the production of a set of pivot tables using
the following macros:

Sub makethemall()

makepivot "TEXT;S:\HE_current\Richard\01projects\Medimmune\flumist
\model\fortran\monthly\A1\results\infectious_no_vacc.csv", _
"PivotTable1"


End Sub


Sub makepivot(filename As String, pivtab As String)

Sheets("Sheet1").Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
"R1C2:R16427C10").CreatePivotTable TableDestination:="",
TableName:=pivtab, DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells
(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables(pivtab).AddFields RowFields:="date"
ActiveSheet.PivotTables(pivtab).PivotFields("0-5 mo").Orientation
= _
xlDataField
ActiveCell.Offset(8, 0).Range("A1").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False,
False, False, _
False, False, False, True)
With ActiveSheet.PivotTables(pivtab).PivotFields("date")
.PivotItems("<01/09/1980").Visible = False
.PivotItems("1980").Visible = False
.PivotItems("1981").Visible = False
.PivotItems("1982").Visible = False
.PivotItems("1983").Visible = False
.PivotItems("1984").Visible = False
.PivotItems("1985").Visible = False
.PivotItems("1986").Visible = False
.PivotItems("1987").Visible = False
.PivotItems("1988").Visible = False
.PivotItems("1989").Visible = False
.PivotItems("1990").Visible = False
.PivotItems("1991").Visible = False
.PivotItems("1992").Visible = False
.PivotItems("1993").Visible = False
.PivotItems("1994").Visible = False
End With
ActiveSheet.PivotTables(pivtab).AddDataField
ActiveSheet.PivotTables( _
pivtab).PivotFields(" 6-23 mo"), "Sum of 6-23 mo", xlSum
ActiveSheet.PivotTables(pivtab).AddDataField
ActiveSheet.PivotTables( _
pivtab).PivotFields(" 24-59 mo"), "Sum of 24-59 mo", xlSum
ActiveSheet.PivotTables(pivtab).AddDataField
ActiveSheet.PivotTables( _
pivtab).PivotFields(" 5-10 years"), "Sum of 5-10 years",
xlSum
ActiveSheet.PivotTables(pivtab).AddDataField
ActiveSheet.PivotTables( _
pivtab).PivotFields(" 11-17 years"), "Sum of 11-17
years", xlSum
ActiveSheet.PivotTables(pivtab).AddDataField
ActiveSheet.PivotTables( _
pivtab).PivotFields(" 18-49 years"), "Sum of 18-49 years",
xlSum
ActiveSheet.PivotTables(pivtab).AddDataField
ActiveSheet.PivotTables( _
pivtab).PivotFields(" 50-64 years"), "Sum of 50-64 years",
xlSum
ActiveSheet.PivotTables(pivtab).AddDataField
ActiveSheet.PivotTables( _
pivtab).PivotFields(" 65+ years"), "Sum of 65+ years", xlSum
ActiveCell.Offset(2, 1).Range("A1").Select
With ActiveSheet.PivotTables(pivtab)
.ColumnGrand = False
.RowGrand = False
End With
ActiveSheet.Select
ActiveSheet.Name = pivtab
Sheets("Sheet1").Select
End Sub

and am getting the error:

Run-Time error "1004"
pivottable filed name is not valid


Any help would be much apreciated.

TIA
 
H

Herbert Seidenberg

Space, - (dash, neg), + (pos)
are not valid characters in a field name.
Upload entire SS for more help.
 

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