pivot table - works manually not by macro

used the excel macro routine to build this macro. did not make any changes to
the code, however, get error 1004 - addfields method of pivot table class
failed'.

when i build the pivot table manually using the same data, i get a pivot
table and no error. so why would microsoft work one way and not the other?

anyway, can you tell me what is wrong with this code?

it is failing at this location
"freqersu6mon!C1:C19").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10


Sub freqersu6mon()
'
' freqersu6mon Macro
' Macro recorded 1/10/2006 by Template
Dim currmonth, curryear, prevyear, prevmonth, datelitl, datex,
prevyear2, lrow
checkslash = Mid(Date, 2, 1)
If checkslash = "/" Then
currmonth = Left(Date, 1)
curryear = Mid(Date, 8, 2)
Else
currmonth = Left(Date, 2)
curryear = Mid(Date, 9, 2)
End If
prevyear2 = curryear - 1
prevyear = "0" & prevyear2

If currmonth = "6" Then
datelitl = "December, 20" & prevyear & " thru May, 20" & curryear
End If
If currmonth = "7" Then
datelitl = "January, 20" & curryear & " thru June, 20" & curryear
End If
If currmonth = "8" Then
datelitl = "February, 20" & curryear & " thru July, 20" & curryear
End If
If currmonth = "9" Then
datelitl = "March, 20" & curryear & " thru August, 20" & curryear
End If
If currmonth = "10" Then
datelitl = "April, 20" & curryear & " thru September, 20" & curryear
End If
If currmonth = "11" Then
datelitl = "May, 20" & curryear & " thru October, 20" & curryear
End If
If currmonth = "12" Then
datelitl = "June, 20" & curryear & " thru November, 20" & curryear
End If
If currmonth = "1" Then
datelitl = "July, 20" & prevyear & " thru December, 20" & prevyear
End If
If currmonth = "2" Then
datelitl = "August, 20" & prevyear & " thru January, 20" & curryear
End If
If currmonth = "3" Then
datelitl = "September, 20" & prevyear & " thru February, 20" & curryear
End If
If currmonth = "4" Then
datelitl = "October, 20" & prevyear & " thru March, 20" & curryear
End If
If currmonth = "5" Then
datelitl = "November, 20" & prevyear & " thru April, 20" & curryear
End If

Rows("1:2").Select
Selection.Delete Shift:=xlUp
Cells.Select
With Selection.Font
.Name = "Garamond"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("C2").Select
Columns("C:C").ColumnWidth = 17
Columns("F:F").ColumnWidth = 44
ActiveWindow.SmallScroll ToRight:=5
Columns("G:G").ColumnWidth = 30.29
Columns("G:G").ColumnWidth = 47.14
ActiveWindow.ScrollColumn = 7
Columns("H:H").ColumnWidth = 39.57
Columns("I:I").ColumnWidth = 33.57
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
Columns("J:J").ColumnWidth = 31.29
Columns("J:J").Select
Columns("K:K").ColumnWidth = 33.71
Range("K18").Select
ActiveWindow.ScrollColumn = 10
Columns("L:L").ColumnWidth = 34.71
Columns("L:L").ColumnWidth = 42.29
ActiveWindow.ScrollColumn = 11
Columns("M:M").ColumnWidth = 42
ActiveWindow.ScrollColumn = 12
Columns("P:P").ColumnWidth = 25.57
ActiveWindow.ScrollColumn = 13
Columns("Q:Q").ColumnWidth = 27.29

Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("F:J").Select
Selection.NumberFormat = "0"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=YEAR(RC[-1])"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=MONTH(RC[-2])"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<10,0,"""")"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-1],RC[-2])"
Range("F2:I2").Select
Selection.Copy
lrow = Range("A" & Rows.Count).End(xlUp).Row
Range("F2:I" & lrow).Select
ActiveSheet.Paste
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Copy
Columns("J:J").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("F:I").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("F1").Select
ActiveCell.FormulaR1C1 = "year month"
Cells.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"freqersu6mon!C1:C19").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Emp Grp", _
ColumnFields:="year month"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("year month")
.Orientation = xlDataField
.Caption = "Total visits"
End With
Application.CommandBars("PivotTable").Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "employer group"
Range("A6").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Emp Grp").Caption = _
"Employer group"
Range("A1").Select
ActiveCell.FormulaR1C1 = "Univera ER managed care data"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Medicare & Blue Choice"
Range("A3").Select
ActiveCell.FormulaR1C1 = datelitl
Sheets("freqersu6mon").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWorkbook.Worksheets("employer group").PivotTables("PivotTable1"). _
PivotCache.CreatePivotTable TableDestination:="",
TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Product", _
ColumnFields:="year month"
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Product")
.Orientation = xlDataField
.Caption = "Total visits"
End With
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Product"
Range("A27").Select
Selection.Delete
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Univera ER managed care data"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Medicare & Blue Choice"
Range("A3").Select
ActiveCell.FormulaR1C1 = datelitl
Range("A8").Select
chdir "E:\adhoc_team\jnewland\erfreqflyer"
ActiveWorkbook.SaveAs Filename:= _
"E:\adhoc_team\jnewland\erfreqflyer\freqersu6mon.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWorkbook.Close

Didn't run the code, but just a thought. Did you try deleting the pivot
table before running the code to create it? The written "code" may work on
the preface that there was no Pivot table to begin with, thus it does
additional work that results in an error.

Try that as an idea... (if this fixes it, then you will have to figure out
how you are going to get around the fact that either you have to modify the
code to do what you want, or cause it to delete the pivot table before
creating it. )

"jnewl" wrote:

> used the excel macro routine to build this macro. did not make any changes to
> the code, however, get error 1004 - addfields method of pivot table class
> failed'.
>
> when i build the pivot table manually using the same data, i get a pivot
> table and no error. so why would microsoft work one way and not the other?
>
> anyway, can you tell me what is wrong with this code?
>
> it is failing at this location
> "freqersu6mon!C1:C19").CreatePivotTable TableDestination:="", TableName:= _
> "PivotTable1", DefaultVersion:=xlPivotTableVersion10
>
>
> Sub freqersu6mon()
> '
> ' freqersu6mon Macro
> ' Macro recorded 1/10/2006 by Template
> Dim currmonth, curryear, prevyear, prevmonth, datelitl, datex,
> prevyear2, lrow
> checkslash = Mid(Date, 2, 1)
> If checkslash = "/" Then
> currmonth = Left(Date, 1)
> curryear = Mid(Date, 8, 2)
> Else
> currmonth = Left(Date, 2)
> curryear = Mid(Date, 9, 2)
> End If
> prevyear2 = curryear - 1
> prevyear = "0" & prevyear2
>
> If currmonth = "6" Then
> datelitl = "December, 20" & prevyear & " thru May, 20" & curryear
> End If
> If currmonth = "7" Then
> datelitl = "January, 20" & curryear & " thru June, 20" & curryear
> End If
> If currmonth = "8" Then
> datelitl = "February, 20" & curryear & " thru July, 20" & curryear
> End If
> If currmonth = "9" Then
> datelitl = "March, 20" & curryear & " thru August, 20" & curryear
> End If
> If currmonth = "10" Then
> datelitl = "April, 20" & curryear & " thru September, 20" & curryear
> End If
> If currmonth = "11" Then
> datelitl = "May, 20" & curryear & " thru October, 20" & curryear
> End If
> If currmonth = "12" Then
> datelitl = "June, 20" & curryear & " thru November, 20" & curryear
> End If
> If currmonth = "1" Then
> datelitl = "July, 20" & prevyear & " thru December, 20" & prevyear
> End If
> If currmonth = "2" Then
> datelitl = "August, 20" & prevyear & " thru January, 20" & curryear
> End If
> If currmonth = "3" Then
> datelitl = "September, 20" & prevyear & " thru February, 20" & curryear
> End If
> If currmonth = "4" Then
> datelitl = "October, 20" & prevyear & " thru March, 20" & curryear
> End If
> If currmonth = "5" Then
> datelitl = "November, 20" & prevyear & " thru April, 20" & curryear
> End If
>
> Rows("1:2").Select
> Selection.Delete Shift:=xlUp
> Cells.Select
> With Selection.Font
> .Name = "Garamond"
> .Size = 10
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = xlAutomatic
> End With
> Range("C2").Select
> Columns("C:C").ColumnWidth = 17
> Columns("F:F").ColumnWidth = 44
> ActiveWindow.SmallScroll ToRight:=5
> Columns("G:G").ColumnWidth = 30.29
> Columns("G:G").ColumnWidth = 47.14
> ActiveWindow.ScrollColumn = 7
> Columns("H:H").ColumnWidth = 39.57
> Columns("I:I").ColumnWidth = 33.57
> ActiveWindow.ScrollColumn = 8
> ActiveWindow.ScrollColumn = 9
> Columns("J:J").ColumnWidth = 31.29
> Columns("J:J").Select
> Columns("K:K").ColumnWidth = 33.71
> Range("K18").Select
> ActiveWindow.ScrollColumn = 10
> Columns("L:L").ColumnWidth = 34.71
> Columns("L:L").ColumnWidth = 42.29
> ActiveWindow.ScrollColumn = 11
> Columns("M:M").ColumnWidth = 42
> ActiveWindow.ScrollColumn = 12
> Columns("P:P").ColumnWidth = 25.57
> ActiveWindow.ScrollColumn = 13
> Columns("Q:Q").ColumnWidth = 27.29
>
> Columns("F:F").Select
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Columns("F:J").Select
> Selection.NumberFormat = "0"
> Range("F2").Select
> ActiveCell.FormulaR1C1 = "=YEAR(RC[-1])"
> Range("G2").Select
> ActiveCell.FormulaR1C1 = "=MONTH(RC[-2])"
> Range("H2").Select
> ActiveCell.FormulaR1C1 = "=IF(RC[-1]<10,0,"""")"
> Range("I2").Select
> ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-1],RC[-2])"
> Range("F2:I2").Select
> Selection.Copy
> lrow = Range("A" & Rows.Count).End(xlUp).Row
> Range("F2:I" & lrow).Select
> ActiveSheet.Paste
> Columns("I:I").Select
> Application.CutCopyMode = False
> Selection.Copy
> Columns("J:J").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Columns("F:I").Select
> Application.CutCopyMode = False
> Selection.Delete Shift:=xlToLeft
> Range("F1").Select
> ActiveCell.FormulaR1C1 = "year month"
> Cells.Select
> ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
> "freqersu6mon!C1:C19").CreatePivotTable TableDestination:="",
> TableName:= _
> "PivotTable1", DefaultVersion:=xlPivotTableVersion10
> ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
> ActiveSheet.Cells(3, 1).Select
> ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Emp Grp", _
> ColumnFields:="year month"
> With ActiveSheet.PivotTables("PivotTable1").PivotFields("year month")
> .Orientation = xlDataField
> .Caption = "Total visits"
> End With
> Application.CommandBars("PivotTable").Visible = False
> ActiveWorkbook.ShowPivotTableFieldList = False
> Rows("3:3").Select
> Selection.Insert Shift:=xlDown
> Selection.Insert Shift:=xlDown
> Sheets("Sheet1").Select
> Sheets("Sheet1").Name = "employer group"
> Range("A6").Select
> ActiveSheet.PivotTables("PivotTable1").PivotFields("Emp Grp").Caption = _
> "Employer group"
> Range("A1").Select
> ActiveCell.FormulaR1C1 = "Univera ER managed care data"
> Range("A2").Select
> ActiveCell.FormulaR1C1 = "Medicare & Blue Choice"
> Range("A3").Select
> ActiveCell.FormulaR1C1 = datelitl
> Sheets("freqersu6mon").Select
> ActiveWindow.ScrollColumn = 2
> ActiveWindow.ScrollColumn = 3
> ActiveWindow.ScrollColumn = 4
> ActiveWindow.ScrollColumn = 5
> ActiveWindow.ScrollColumn = 6
> ActiveWorkbook.Worksheets("employer group").PivotTables("PivotTable1"). _
> PivotCache.CreatePivotTable TableDestination:="",
> TableName:="PivotTable2" _
> , DefaultVersion:=xlPivotTableVersion10
> ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Product", _
> ColumnFields:="year month"
> With ActiveSheet.PivotTables("PivotTable2").PivotFields("Product")
> .Orientation = xlDataField
> .Caption = "Total visits"
> End With
> ActiveWorkbook.ShowPivotTableFieldList = True
> ActiveWorkbook.ShowPivotTableFieldList = False
> Application.CommandBars("PivotTable").Visible = False
> Sheets("Sheet2").Select
> Sheets("Sheet2").Name = "Product"
> Range("A27").Select
> Selection.Delete
> Rows("2:2").Select
> Selection.Insert Shift:=xlDown
> Selection.Insert Shift:=xlDown
> Range("A1").Select
> ActiveCell.FormulaR1C1 = "Univera ER managed care data"
> Range("A2").Select
> ActiveCell.FormulaR1C1 = "Medicare & Blue Choice"
> Range("A3").Select
> ActiveCell.FormulaR1C1 = datelitl
> Range("A8").Select
> chdir "E:\adhoc_team\jnewland\erfreqflyer"
> ActiveWorkbook.SaveAs Filename:= _
> "E:\adhoc_team\jnewland\erfreqflyer\freqersu6mon.xls", FileFormat:= _
> xlExcel9795, Password:="", WriteResPassword:="",
> ReadOnlyRecommended:= _
> False, CreateBackup:=False
> ActiveWorkbook.Close
>
>

Looking at the line that is erroring, it looks like it is trying to do what I
described, create a pivot table that is already created.. PivotTable1. I
doubt that the program likes that aspect.. If however, you do not want to
destroy the table before running this code, you can assign an

on error next

Or something similar, so that it keeps on chugging along... If the table
exists and you want it to be persistent, then hey who cares if it errors in
trying to recreate the table.


"jnewl" wrote:

> used the excel macro routine to build this macro. did not make any changes to
> the code, however, get error 1004 - addfields method of pivot table class
> failed'.
>
> when i build the pivot table manually using the same data, i get a pivot
> table and no error. so why would microsoft work one way and not the other?
>
> anyway, can you tell me what is wrong with this code?
>
> it is failing at this location
> "freqersu6mon!C1:C19").CreatePivotTable TableDestination:="", TableName:= _
> "PivotTable1", DefaultVersion:=xlPivotTableVersion10
>
>
> Sub freqersu6mon()
> '
> ' freqersu6mon Macro
> ' Macro recorded 1/10/2006 by Template
> Dim currmonth, curryear, prevyear, prevmonth, datelitl, datex,
> prevyear2, lrow
> checkslash = Mid(Date, 2, 1)
> If checkslash = "/" Then
> currmonth = Left(Date, 1)
> curryear = Mid(Date, 8, 2)
> Else
> currmonth = Left(Date, 2)
> curryear = Mid(Date, 9, 2)
> End If
> prevyear2 = curryear - 1
> prevyear = "0" & prevyear2
>
> If currmonth = "6" Then
> datelitl = "December, 20" & prevyear & " thru May, 20" & curryear
> End If
> If currmonth = "7" Then
> datelitl = "January, 20" & curryear & " thru June, 20" & curryear
> End If
> If currmonth = "8" Then
> datelitl = "February, 20" & curryear & " thru July, 20" & curryear
> End If
> If currmonth = "9" Then
> datelitl = "March, 20" & curryear & " thru August, 20" & curryear
> End If
> If currmonth = "10" Then
> datelitl = "April, 20" & curryear & " thru September, 20" & curryear
> End If
> If currmonth = "11" Then
> datelitl = "May, 20" & curryear & " thru October, 20" & curryear
> End If
> If currmonth = "12" Then
> datelitl = "June, 20" & curryear & " thru November, 20" & curryear
> End If
> If currmonth = "1" Then
> datelitl = "July, 20" & prevyear & " thru December, 20" & prevyear
> End If
> If currmonth = "2" Then
> datelitl = "August, 20" & prevyear & " thru January, 20" & curryear
> End If
> If currmonth = "3" Then
> datelitl = "September, 20" & prevyear & " thru February, 20" & curryear
> End If
> If currmonth = "4" Then
> datelitl = "October, 20" & prevyear & " thru March, 20" & curryear
> End If
> If currmonth = "5" Then
> datelitl = "November, 20" & prevyear & " thru April, 20" & curryear
> End If
>
> Rows("1:2").Select
> Selection.Delete Shift:=xlUp
> Cells.Select
> With Selection.Font
> .Name = "Garamond"
> .Size = 10
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = xlAutomatic
> End With
> Range("C2").Select
> Columns("C:C").ColumnWidth = 17
> Columns("F:F").ColumnWidth = 44
> ActiveWindow.SmallScroll ToRight:=5
> Columns("G:G").ColumnWidth = 30.29
> Columns("G:G").ColumnWidth = 47.14
> ActiveWindow.ScrollColumn = 7
> Columns("H:H").ColumnWidth = 39.57
> Columns("I:I").ColumnWidth = 33.57
> ActiveWindow.ScrollColumn = 8
> ActiveWindow.ScrollColumn = 9
> Columns("J:J").ColumnWidth = 31.29
> Columns("J:J").Select
> Columns("K:K").ColumnWidth = 33.71
> Range("K18").Select
> ActiveWindow.ScrollColumn = 10
> Columns("L:L").ColumnWidth = 34.71
> Columns("L:L").ColumnWidth = 42.29
> ActiveWindow.ScrollColumn = 11
> Columns("M:M").ColumnWidth = 42
> ActiveWindow.ScrollColumn = 12
> Columns("P:P").ColumnWidth = 25.57
> ActiveWindow.ScrollColumn = 13
> Columns("Q:Q").ColumnWidth = 27.29
>
> Columns("F:F").Select
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Columns("F:J").Select
> Selection.NumberFormat = "0"
> Range("F2").Select
> ActiveCell.FormulaR1C1 = "=YEAR(RC[-1])"
> Range("G2").Select
> ActiveCell.FormulaR1C1 = "=MONTH(RC[-2])"
> Range("H2").Select
> ActiveCell.FormulaR1C1 = "=IF(RC[-1]<10,0,"""")"
> Range("I2").Select
> ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-1],RC[-2])"
> Range("F2:I2").Select
> Selection.Copy
> lrow = Range("A" & Rows.Count).End(xlUp).Row
> Range("F2:I" & lrow).Select
> ActiveSheet.Paste
> Columns("I:I").Select
> Application.CutCopyMode = False
> Selection.Copy
> Columns("J:J").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Columns("F:I").Select
> Application.CutCopyMode = False
> Selection.Delete Shift:=xlToLeft
> Range("F1").Select
> ActiveCell.FormulaR1C1 = "year month"
> Cells.Select
> ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
> "freqersu6mon!C1:C19").CreatePivotTable TableDestination:="",
> TableName:= _
> "PivotTable1", DefaultVersion:=xlPivotTableVersion10
> ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
> ActiveSheet.Cells(3, 1).Select
> ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Emp Grp", _
> ColumnFields:="year month"
> With ActiveSheet.PivotTables("PivotTable1").PivotFields("year month")
> .Orientation = xlDataField
> .Caption = "Total visits"
> End With
> Application.CommandBars("PivotTable").Visible = False
> ActiveWorkbook.ShowPivotTableFieldList = False
> Rows("3:3").Select
> Selection.Insert Shift:=xlDown
> Selection.Insert Shift:=xlDown
> Sheets("Sheet1").Select
> Sheets("Sheet1").Name = "employer group"
> Range("A6").Select
> ActiveSheet.PivotTables("PivotTable1").PivotFields("Emp Grp").Caption = _
> "Employer group"
> Range("A1").Select
> ActiveCell.FormulaR1C1 = "Univera ER managed care data"
> Range("A2").Select
> ActiveCell.FormulaR1C1 = "Medicare & Blue Choice"
> Range("A3").Select
> ActiveCell.FormulaR1C1 = datelitl
> Sheets("freqersu6mon").Select
> ActiveWindow.ScrollColumn = 2
> ActiveWindow.ScrollColumn = 3
> ActiveWindow.ScrollColumn = 4
> ActiveWindow.ScrollColumn = 5
> ActiveWindow.ScrollColumn = 6
> ActiveWorkbook.Worksheets("employer group").PivotTables("PivotTable1"). _
> PivotCache.CreatePivotTable TableDestination:="",
> TableName:="PivotTable2" _
> , DefaultVersion:=xlPivotTableVersion10
> ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Product", _
> ColumnFields:="year month"
> With ActiveSheet.PivotTables("PivotTable2").PivotFields("Product")
> .Orientation = xlDataField
> .Caption = "Total visits"
> End With
> ActiveWorkbook.ShowPivotTableFieldList = True
> ActiveWorkbook.ShowPivotTableFieldList = False
> Application.CommandBars("PivotTable").Visible = False
> Sheets("Sheet2").Select
> Sheets("Sheet2").Name = "Product"
> Range("A27").Select
> Selection.Delete
> Rows("2:2").Select
> Selection.Insert Shift:=xlDown
> Selection.Insert Shift:=xlDown
> Range("A1").Select
> ActiveCell.FormulaR1C1 = "Univera ER managed care data"
> Range("A2").Select
> ActiveCell.FormulaR1C1 = "Medicare & Blue Choice"
> Range("A3").Select
> ActiveCell.FormulaR1C1 = datelitl
> Range("A8").Select
> chdir "E:\adhoc_team\jnewland\erfreqflyer"
> ActiveWorkbook.SaveAs Filename:= _
> "E:\adhoc_team\jnewland\erfreqflyer\freqersu6mon.xls", FileFormat:= _
> xlExcel9795, Password:="", WriteResPassword:="",
> ReadOnlyRecommended:= _
> False, CreateBackup:=False
> ActiveWorkbook.Close
>
>

thanks, will try it

"GB" wrote:

> Looking at the line that is erroring, it looks like it is trying to do what I
> described, create a pivot table that is already created.. PivotTable1. I
> doubt that the program likes that aspect.. If however, you do not want to
> destroy the table before running this code, you can assign an
>
> on error next
>
> Or something similar, so that it keeps on chugging along... If the table
> exists and you want it to be persistent, then hey who cares if it errors in
> trying to recreate the table.
>
>
> "jnewl" wrote:
>
> > used the excel macro routine to build this macro. did not make any changes to
> > the code, however, get error 1004 - addfields method of pivot table class
> > failed'.
> >
> > when i build the pivot table manually using the same data, i get a pivot
> > table and no error. so why would microsoft work one way and not the other?
> >
> > anyway, can you tell me what is wrong with this code?
> >
> > it is failing at this location
> > "freqersu6mon!C1:C19").CreatePivotTable TableDestination:="", TableName:= _
> > "PivotTable1", DefaultVersion:=xlPivotTableVersion10
> >
> >
> > Sub freqersu6mon()
> > '
> > ' freqersu6mon Macro
> > ' Macro recorded 1/10/2006 by Template
> > Dim currmonth, curryear, prevyear, prevmonth, datelitl, datex,
> > prevyear2, lrow
> > checkslash = Mid(Date, 2, 1)
> > If checkslash = "/" Then
> > currmonth = Left(Date, 1)
> > curryear = Mid(Date, 8, 2)
> > Else
> > currmonth = Left(Date, 2)
> > curryear = Mid(Date, 9, 2)
> > End If
> > prevyear2 = curryear - 1
> > prevyear = "0" & prevyear2
> >
> > If currmonth = "6" Then
> > datelitl = "December, 20" & prevyear & " thru May, 20" & curryear
> > End If
> > If currmonth = "7" Then
> > datelitl = "January, 20" & curryear & " thru June, 20" & curryear
> > End If
> > If currmonth = "8" Then
> > datelitl = "February, 20" & curryear & " thru July, 20" & curryear
> > End If
> > If currmonth = "9" Then
> > datelitl = "March, 20" & curryear & " thru August, 20" & curryear
> > End If
> > If currmonth = "10" Then
> > datelitl = "April, 20" & curryear & " thru September, 20" & curryear
> > End If
> > If currmonth = "11" Then
> > datelitl = "May, 20" & curryear & " thru October, 20" & curryear
> > End If
> > If currmonth = "12" Then
> > datelitl = "June, 20" & curryear & " thru November, 20" & curryear
> > End If
> > If currmonth = "1" Then
> > datelitl = "July, 20" & prevyear & " thru December, 20" & prevyear
> > End If
> > If currmonth = "2" Then
> > datelitl = "August, 20" & prevyear & " thru January, 20" & curryear
> > End If
> > If currmonth = "3" Then
> > datelitl = "September, 20" & prevyear & " thru February, 20" & curryear
> > End If
> > If currmonth = "4" Then
> > datelitl = "October, 20" & prevyear & " thru March, 20" & curryear
> > End If
> > If currmonth = "5" Then
> > datelitl = "November, 20" & prevyear & " thru April, 20" & curryear
> > End If
> >
> > Rows("1:2").Select
> > Selection.Delete Shift:=xlUp
> > Cells.Select
> > With Selection.Font
> > .Name = "Garamond"
> > .Size = 10
> > .Strikethrough = False
> > .Superscript = False
> > .Subscript = False
> > .OutlineFont = False
> > .Shadow = False
> > .Underline = xlUnderlineStyleNone
> > .ColorIndex = xlAutomatic
> > End With
> > Range("C2").Select
> > Columns("C:C").ColumnWidth = 17
> > Columns("F:F").ColumnWidth = 44
> > ActiveWindow.SmallScroll ToRight:=5
> > Columns("G:G").ColumnWidth = 30.29
> > Columns("G:G").ColumnWidth = 47.14
> > ActiveWindow.ScrollColumn = 7
> > Columns("H:H").ColumnWidth = 39.57
> > Columns("I:I").ColumnWidth = 33.57
> > ActiveWindow.ScrollColumn = 8
> > ActiveWindow.ScrollColumn = 9
> > Columns("J:J").ColumnWidth = 31.29
> > Columns("J:J").Select
> > Columns("K:K").ColumnWidth = 33.71
> > Range("K18").Select
> > ActiveWindow.ScrollColumn = 10
> > Columns("L:L").ColumnWidth = 34.71
> > Columns("L:L").ColumnWidth = 42.29
> > ActiveWindow.ScrollColumn = 11
> > Columns("M:M").ColumnWidth = 42
> > ActiveWindow.ScrollColumn = 12
> > Columns("P:P").ColumnWidth = 25.57
> > ActiveWindow.ScrollColumn = 13
> > Columns("Q:Q").ColumnWidth = 27.29
> >
> > Columns("F:F").Select
> > Selection.Insert Shift:=xlToRight
> > Selection.Insert Shift:=xlToRight
> > Selection.Insert Shift:=xlToRight
> > Selection.Insert Shift:=xlToRight
> > Selection.Insert Shift:=xlToRight
> > Columns("F:J").Select
> > Selection.NumberFormat = "0"
> > Range("F2").Select
> > ActiveCell.FormulaR1C1 = "=YEAR(RC[-1])"
> > Range("G2").Select
> > ActiveCell.FormulaR1C1 = "=MONTH(RC[-2])"
> > Range("H2").Select
> > ActiveCell.FormulaR1C1 = "=IF(RC[-1]<10,0,"""")"
> > Range("I2").Select
> > ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-1],RC[-2])"
> > Range("F2:I2").Select
> > Selection.Copy
> > lrow = Range("A" & Rows.Count).End(xlUp).Row
> > Range("F2:I" & lrow).Select
> > ActiveSheet.Paste
> > Columns("I:I").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Columns("J:J").Select
> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=False, Transpose:=False
> > Columns("F:I").Select
> > Application.CutCopyMode = False
> > Selection.Delete Shift:=xlToLeft
> > Range("F1").Select
> > ActiveCell.FormulaR1C1 = "year month"
> > Cells.Select
> > ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
> > "freqersu6mon!C1:C19").CreatePivotTable TableDestination:="",
> > TableName:= _
> > "PivotTable1", DefaultVersion:=xlPivotTableVersion10
> > ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
> > ActiveSheet.Cells(3, 1).Select
> > ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Emp Grp", _
> > ColumnFields:="year month"
> > With ActiveSheet.PivotTables("PivotTable1").PivotFields("year month")
> > .Orientation = xlDataField
> > .Caption = "Total visits"
> > End With
> > Application.CommandBars("PivotTable").Visible = False
> > ActiveWorkbook.ShowPivotTableFieldList = False
> > Rows("3:3").Select
> > Selection.Insert Shift:=xlDown
> > Selection.Insert Shift:=xlDown
> > Sheets("Sheet1").Select
> > Sheets("Sheet1").Name = "employer group"
> > Range("A6").Select
> > ActiveSheet.PivotTables("PivotTable1").PivotFields("Emp Grp").Caption = _
> > "Employer group"
> > Range("A1").Select
> > ActiveCell.FormulaR1C1 = "Univera ER managed care data"
> > Range("A2").Select
> > ActiveCell.FormulaR1C1 = "Medicare & Blue Choice"
> > Range("A3").Select
> > ActiveCell.FormulaR1C1 = datelitl
> > Sheets("freqersu6mon").Select
> > ActiveWindow.ScrollColumn = 2
> > ActiveWindow.ScrollColumn = 3
> > ActiveWindow.ScrollColumn = 4
> > ActiveWindow.ScrollColumn = 5
> > ActiveWindow.ScrollColumn = 6
> > ActiveWorkbook.Worksheets("employer group").PivotTables("PivotTable1"). _
> > PivotCache.CreatePivotTable TableDestination:="",
> > TableName:="PivotTable2" _
> > , DefaultVersion:=xlPivotTableVersion10
> > ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Product", _
> > ColumnFields:="year month"
> > With ActiveSheet.PivotTables("PivotTable2").PivotFields("Product")
> > .Orientation = xlDataField
> > .Caption = "Total visits"
> > End With
> > ActiveWorkbook.ShowPivotTableFieldList = True
> > ActiveWorkbook.ShowPivotTableFieldList = False
> > Application.CommandBars("PivotTable").Visible = False
> > Sheets("Sheet2").Select
> > Sheets("Sheet2").Name = "Product"
> > Range("A27").Select
> > Selection.Delete
> > Rows("2:2").Select
> > Selection.Insert Shift:=xlDown
> > Selection.Insert Shift:=xlDown
> > Range("A1").Select
> > ActiveCell.FormulaR1C1 = "Univera ER managed care data"
> > Range("A2").Select
> > ActiveCell.FormulaR1C1 = "Medicare & Blue Choice"
> > Range("A3").Select
> > ActiveCell.FormulaR1C1 = datelitl
> > Range("A8").Select
> > chdir "E:\adhoc_team\jnewland\erfreqflyer"
> > ActiveWorkbook.SaveAs Filename:= _
> > "E:\adhoc_team\jnewland\erfreqflyer\freqersu6mon.xls", FileFormat:= _
> > xlExcel9795, Password:="", WriteResPassword:="",
> > ReadOnlyRecommended:= _
> > False, CreateBackup:=False
> > ActiveWorkbook.Close
> >
> >

hi,
i must be coding wrong. can not get it to work with deleting the pt or
doing an on error.
i deleted the pt right before the failure line.
was using on error resume next for the 2nd possibility

so need some help in how to fix.

thanks



"GB" wrote:

> Looking at the line that is erroring, it looks like it is trying to do what I
> described, create a pivot table that is already created.. PivotTable1. I
> doubt that the program likes that aspect.. If however, you do not want to
> destroy the table before running this code, you can assign an
>
> on error next
>
> Or something similar, so that it keeps on chugging along... If the table
> exists and you want it to be persistent, then hey who cares if it errors in
> trying to recreate the table.
>
>
> "jnewl" wrote:
>
> > used the excel macro routine to build this macro. did not make any changes to
> > the code, however, get error 1004 - addfields method of pivot table class
> > failed'.
> >
> > when i build the pivot table manually using the same data, i get a pivot
> > table and no error. so why would microsoft work one way and not the other?
> >
> > anyway, can you tell me what is wrong with this code?
> >
> > it is failing at this location
> > "freqersu6mon!C1:C19").CreatePivotTable TableDestination:="", TableName:= _
> > "PivotTable1", DefaultVersion:=xlPivotTableVersion10
> >
> >
> > Sub freqersu6mon()
> > '
> > ' freqersu6mon Macro
> > ' Macro recorded 1/10/2006 by Template
> > Dim currmonth, curryear, prevyear, prevmonth, datelitl, datex,
> > prevyear2, lrow
> > checkslash = Mid(Date, 2, 1)
> > If checkslash = "/" Then
> > currmonth = Left(Date, 1)
> > curryear = Mid(Date, 8, 2)
> > Else
> > currmonth = Left(Date, 2)
> > curryear = Mid(Date, 9, 2)
> > End If
> > prevyear2 = curryear - 1
> > prevyear = "0" & prevyear2
> >
> > If currmonth = "6" Then
> > datelitl = "December, 20" & prevyear & " thru May, 20" & curryear
> > End If
> > If currmonth = "7" Then
> > datelitl = "January, 20" & curryear & " thru June, 20" & curryear
> > End If
> > If currmonth = "8" Then
> > datelitl = "February, 20" & curryear & " thru July, 20" & curryear
> > End If
> > If currmonth = "9" Then
> > datelitl = "March, 20" & curryear & " thru August, 20" & curryear
> > End If
> > If currmonth = "10" Then
> > datelitl = "April, 20" & curryear & " thru September, 20" & curryear
> > End If
> > If currmonth = "11" Then
> > datelitl = "May, 20" & curryear & " thru October, 20" & curryear
> > End If
> > If currmonth = "12" Then
> > datelitl = "June, 20" & curryear & " thru November, 20" & curryear
> > End If
> > If currmonth = "1" Then
> > datelitl = "July, 20" & prevyear & " thru December, 20" & prevyear
> > End If
> > If currmonth = "2" Then
> > datelitl = "August, 20" & prevyear & " thru January, 20" & curryear
> > End If
> > If currmonth = "3" Then
> > datelitl = "September, 20" & prevyear & " thru February, 20" & curryear
> > End If
> > If currmonth = "4" Then
> > datelitl = "October, 20" & prevyear & " thru March, 20" & curryear
> > End If
> > If currmonth = "5" Then
> > datelitl = "November, 20" & prevyear & " thru April, 20" & curryear
> > End If
> >
> > Rows("1:2").Select
> > Selection.Delete Shift:=xlUp
> > Cells.Select
> > With Selection.Font
> > .Name = "Garamond"
> > .Size = 10
> > .Strikethrough = False
> > .Superscript = False
> > .Subscript = False
> > .OutlineFont = False
> > .Shadow = False
> > .Underline = xlUnderlineStyleNone
> > .ColorIndex = xlAutomatic
> > End With
> > Range("C2").Select
> > Columns("C:C").ColumnWidth = 17
> > Columns("F:F").ColumnWidth = 44
> > ActiveWindow.SmallScroll ToRight:=5
> > Columns("G:G").ColumnWidth = 30.29
> > Columns("G:G").ColumnWidth = 47.14
> > ActiveWindow.ScrollColumn = 7
> > Columns("H:H").ColumnWidth = 39.57
> > Columns("I:I").ColumnWidth = 33.57
> > ActiveWindow.ScrollColumn = 8
> > ActiveWindow.ScrollColumn = 9
> > Columns("J:J").ColumnWidth = 31.29
> > Columns("J:J").Select
> > Columns("K:K").ColumnWidth = 33.71
> > Range("K18").Select
> > ActiveWindow.ScrollColumn = 10
> > Columns("L:L").ColumnWidth = 34.71
> > Columns("L:L").ColumnWidth = 42.29
> > ActiveWindow.ScrollColumn = 11
> > Columns("M:M").ColumnWidth = 42
> > ActiveWindow.ScrollColumn = 12
> > Columns("P:P").ColumnWidth = 25.57
> > ActiveWindow.ScrollColumn = 13
> > Columns("Q:Q").ColumnWidth = 27.29
> >
> > Columns("F:F").Select
> > Selection.Insert Shift:=xlToRight
> > Selection.Insert Shift:=xlToRight
> > Selection.Insert Shift:=xlToRight
> > Selection.Insert Shift:=xlToRight
> > Selection.Insert Shift:=xlToRight
> > Columns("F:J").Select
> > Selection.NumberFormat = "0"
> > Range("F2").Select
> > ActiveCell.FormulaR1C1 = "=YEAR(RC[-1])"
> > Range("G2").Select
> > ActiveCell.FormulaR1C1 = "=MONTH(RC[-2])"
> > Range("H2").Select
> > ActiveCell.FormulaR1C1 = "=IF(RC[-1]<10,0,"""")"
> > Range("I2").Select
> > ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-1],RC[-2])"
> > Range("F2:I2").Select
> > Selection.Copy
> > lrow = Range("A" & Rows.Count).End(xlUp).Row
> > Range("F2:I" & lrow).Select
> > ActiveSheet.Paste
> > Columns("I:I").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Columns("J:J").Select
> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=False, Transpose:=False
> > Columns("F:I").Select
> > Application.CutCopyMode = False
> > Selection.Delete Shift:=xlToLeft
> > Range("F1").Select
> > ActiveCell.FormulaR1C1 = "year month"
> > Cells.Select
> > ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
> > "freqersu6mon!C1:C19").CreatePivotTable TableDestination:="",
> > TableName:= _
> > "PivotTable1", DefaultVersion:=xlPivotTableVersion10
> > ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
> > ActiveSheet.Cells(3, 1).Select
> > ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Emp Grp", _
> > ColumnFields:="year month"
> > With ActiveSheet.PivotTables("PivotTable1").PivotFields("year month")
> > .Orientation = xlDataField
> > .Caption = "Total visits"
> > End With
> > Application.CommandBars("PivotTable").Visible = False
> > ActiveWorkbook.ShowPivotTableFieldList = False
> > Rows("3:3").Select
> > Selection.Insert Shift:=xlDown
> > Selection.Insert Shift:=xlDown
> > Sheets("Sheet1").Select
> > Sheets("Sheet1").Name = "employer group"
> > Range("A6").Select
> > ActiveSheet.PivotTables("PivotTable1").PivotFields("Emp Grp").Caption = _
> > "Employer group"
> > Range("A1").Select
> > ActiveCell.FormulaR1C1 = "Univera ER managed care data"
> > Range("A2").Select
> > ActiveCell.FormulaR1C1 = "Medicare & Blue Choice"
> > Range("A3").Select
> > ActiveCell.FormulaR1C1 = datelitl
> > Sheets("freqersu6mon").Select
> > ActiveWindow.ScrollColumn = 2
> > ActiveWindow.ScrollColumn = 3
> > ActiveWindow.ScrollColumn = 4
> > ActiveWindow.ScrollColumn = 5
> > ActiveWindow.ScrollColumn = 6
> > ActiveWorkbook.Worksheets("employer group").PivotTables("PivotTable1"). _
> > PivotCache.CreatePivotTable TableDestination:="",
> > TableName:="PivotTable2" _
> > , DefaultVersion:=xlPivotTableVersion10
> > ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Product", _
> > ColumnFields:="year month"
> > With ActiveSheet.PivotTables("PivotTable2").PivotFields("Product")
> > .Orientation = xlDataField
> > .Caption = "Total visits"
> > End With
> > ActiveWorkbook.ShowPivotTableFieldList = True
> > ActiveWorkbook.ShowPivotTableFieldList = False
> > Application.CommandBars("PivotTable").Visible = False
> > Sheets("Sheet2").Select
> > Sheets("Sheet2").Name = "Product"
> > Range("A27").Select
> > Selection.Delete
> > Rows("2:2").Select
> > Selection.Insert Shift:=xlDown
> > Selection.Insert Shift:=xlDown
> > Range("A1").Select
> > ActiveCell.FormulaR1C1 = "Univera ER managed care data"
> > Range("A2").Select
> > ActiveCell.FormulaR1C1 = "Medicare & Blue Choice"
> > Range("A3").Select
> > ActiveCell.FormulaR1C1 = datelitl
> > Range("A8").Select
> > chdir "E:\adhoc_team\jnewland\erfreqflyer"
> > ActiveWorkbook.SaveAs Filename:= _
> > "E:\adhoc_team\jnewland\erfreqflyer\freqersu6mon.xls", FileFormat:= _
> > xlExcel9795, Password:="", WriteResPassword:="",
> > ReadOnlyRecommended:= _
> > False, CreateBackup:=False
> > ActiveWorkbook.Close
> >
> >

i tried using the on error and deleting the pivot table, to no avail.
so, i must be coding wrong.

i found an entry in this library where someone asked how to delete a pivot
table.

so i used this code
ws.pivottables(1).databodyrange_
.currentregion.entirecolumn.delete

ws is defined as worksheet.

i get an error 'invalid or unqualified reference'.
if i put everything on one line, i get 'object variable or with block
variable'.

so need your help on how can solve this issue.
just to recap, i built a pivot table manually with no probs. recorded a
macro doing the same steps. did not change any of the macro code. when i run
macro, it abends

"jnewl" wrote:

> used the excel macro routine to build this macro. did not make any changes to
> the code, however, get error 1004 - addfields method of pivot table class
> failed'.
>
> when i build the pivot table manually using the same data, i get a pivot
> table and no error. so why would microsoft work one way and not the other?
>
> anyway, can you tell me what is wrong with this code?
>
> it is failing at this location
> "freqersu6mon!C1:C19").CreatePivotTable TableDestination:="", TableName:= _
> "PivotTable1", DefaultVersion:=xlPivotTableVersion10
>
>
> Sub freqersu6mon()
> '
> ' freqersu6mon Macro
> ' Macro recorded 1/10/2006 by Template
> Dim currmonth, curryear, prevyear, prevmonth, datelitl, datex,
> prevyear2, lrow
> checkslash = Mid(Date, 2, 1)
> If checkslash = "/" Then
> currmonth = Left(Date, 1)
> curryear = Mid(Date, 8, 2)
> Else
> currmonth = Left(Date, 2)
> curryear = Mid(Date, 9, 2)
> End If
> prevyear2 = curryear - 1
> prevyear = "0" & prevyear2
>
> If currmonth = "6" Then
> datelitl = "December, 20" & prevyear & " thru May, 20" & curryear
> End If
> If currmonth = "7" Then
> datelitl = "January, 20" & curryear & " thru June, 20" & curryear
> End If
> If currmonth = "8" Then
> datelitl = "February, 20" & curryear & " thru July, 20" & curryear
> End If
> If currmonth = "9" Then
> datelitl = "March, 20" & curryear & " thru August, 20" & curryear
> End If
> If currmonth = "10" Then
> datelitl = "April, 20" & curryear & " thru September, 20" & curryear
> End If
> If currmonth = "11" Then
> datelitl = "May, 20" & curryear & " thru October, 20" & curryear
> End If
> If currmonth = "12" Then
> datelitl = "June, 20" & curryear & " thru November, 20" & curryear
> End If
> If currmonth = "1" Then
> datelitl = "July, 20" & prevyear & " thru December, 20" & prevyear
> End If
> If currmonth = "2" Then
> datelitl = "August, 20" & prevyear & " thru January, 20" & curryear
> End If
> If currmonth = "3" Then
> datelitl = "September, 20" & prevyear & " thru February, 20" & curryear
> End If
> If currmonth = "4" Then
> datelitl = "October, 20" & prevyear & " thru March, 20" & curryear
> End If
> If currmonth = "5" Then
> datelitl = "November, 20" & prevyear & " thru April, 20" & curryear
> End If
>
> Rows("1:2").Select
> Selection.Delete Shift:=xlUp
> Cells.Select
> With Selection.Font
> .Name = "Garamond"
> .Size = 10
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = xlAutomatic
> End With
> Range("C2").Select
> Columns("C:C").ColumnWidth = 17
> Columns("F:F").ColumnWidth = 44
> ActiveWindow.SmallScroll ToRight:=5
> Columns("G:G").ColumnWidth = 30.29
> Columns("G:G").ColumnWidth = 47.14
> ActiveWindow.ScrollColumn = 7
> Columns("H:H").ColumnWidth = 39.57
> Columns("I:I").ColumnWidth = 33.57
> ActiveWindow.ScrollColumn = 8
> ActiveWindow.ScrollColumn = 9
> Columns("J:J").ColumnWidth = 31.29
> Columns("J:J").Select
> Columns("K:K").ColumnWidth = 33.71
> Range("K18").Select
> ActiveWindow.ScrollColumn = 10
> Columns("L:L").ColumnWidth = 34.71
> Columns("L:L").ColumnWidth = 42.29
> ActiveWindow.ScrollColumn = 11
> Columns("M:M").ColumnWidth = 42
> ActiveWindow.ScrollColumn = 12
> Columns("P:P").ColumnWidth = 25.57
> ActiveWindow.ScrollColumn = 13
> Columns("Q:Q").ColumnWidth = 27.29
>
> Columns("F:F").Select
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Columns("F:J").Select
> Selection.NumberFormat = "0"
> Range("F2").Select
> ActiveCell.FormulaR1C1 = "=YEAR(RC[-1])"
> Range("G2").Select
> ActiveCell.FormulaR1C1 = "=MONTH(RC[-2])"
> Range("H2").Select
> ActiveCell.FormulaR1C1 = "=IF(RC[-1]<10,0,"""")"
> Range("I2").Select
> ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-1],RC[-2])"
> Range("F2:I2").Select
> Selection.Copy
> lrow = Range("A" & Rows.Count).End(xlUp).Row
> Range("F2:I" & lrow).Select
> ActiveSheet.Paste
> Columns("I:I").Select
> Application.CutCopyMode = False
> Selection.Copy
> Columns("J:J").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Columns("F:I").Select
> Application.CutCopyMode = False
> Selection.Delete Shift:=xlToLeft
> Range("F1").Select
> ActiveCell.FormulaR1C1 = "year month"
> Cells.Select
> ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
> "freqersu6mon!C1:C19").CreatePivotTable TableDestination:="",
> TableName:= _
> "PivotTable1", DefaultVersion:=xlPivotTableVersion10
> ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
> ActiveSheet.Cells(3, 1).Select
> ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Emp Grp", _
> ColumnFields:="year month"
> With ActiveSheet.PivotTables("PivotTable1").PivotFields("year month")
> .Orientation = xlDataField
> .Caption = "Total visits"
> End With
> Application.CommandBars("PivotTable").Visible = False
> ActiveWorkbook.ShowPivotTableFieldList = False
> Rows("3:3").Select
> Selection.Insert Shift:=xlDown
> Selection.Insert Shift:=xlDown
> Sheets("Sheet1").Select
> Sheets("Sheet1").Name = "employer group"
> Range("A6").Select
> ActiveSheet.PivotTables("PivotTable1").PivotFields("Emp Grp").Caption = _
> "Employer group"
> Range("A1").Select
> ActiveCell.FormulaR1C1 = "Univera ER managed care data"
> Range("A2").Select
> ActiveCell.FormulaR1C1 = "Medicare & Blue Choice"
> Range("A3").Select
> ActiveCell.FormulaR1C1 = datelitl
> Sheets("freqersu6mon").Select
> ActiveWindow.ScrollColumn = 2
> ActiveWindow.ScrollColumn = 3
> ActiveWindow.ScrollColumn = 4
> ActiveWindow.ScrollColumn = 5
> ActiveWindow.ScrollColumn = 6
> ActiveWorkbook.Worksheets("employer group").PivotTables("PivotTable1"). _
> PivotCache.CreatePivotTable TableDestination:="",
> TableName:="PivotTable2" _
> , DefaultVersion:=xlPivotTableVersion10
> ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Product", _
> ColumnFields:="year month"
> With ActiveSheet.PivotTables("PivotTable2").PivotFields("Product")
> .Orientation = xlDataField
> .Caption = "Total visits"
> End With
> ActiveWorkbook.ShowPivotTableFieldList = True
> ActiveWorkbook.ShowPivotTableFieldList = False
> Application.CommandBars("PivotTable").Visible = False
> Sheets("Sheet2").Select
> Sheets("Sheet2").Name = "Product"
> Range("A27").Select
> Selection.Delete
> Rows("2:2").Select
> Selection.Insert Shift:=xlDown
> Selection.Insert Shift:=xlDown
> Range("A1").Select
> ActiveCell.FormulaR1C1 = "Univera ER managed care data"
> Range("A2").Select
> ActiveCell.FormulaR1C1 = "Medicare & Blue Choice"
> Range("A3").Select
> ActiveCell.FormulaR1C1 = datelitl
> Range("A8").Select
> chdir "E:\adhoc_team\jnewland\erfreqflyer"
> ActiveWorkbook.SaveAs Filename:= _
> "E:\adhoc_team\jnewland\erfreqflyer\freqersu6mon.xls", FileFormat:= _
> xlExcel9795, Password:="", WriteResPassword:="",
> ReadOnlyRecommended:= _
> False, CreateBackup:=False
> ActiveWorkbook.Close
>
>