pivot table - works manually not by macro

J

jnewl

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
 
G

GB

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 said:
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
Click to expand...
 
G

GB

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 said:
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
Click to expand...
 
J

jnewl

thanks, will try it

GB said:
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 said:
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
Click to expand...
Click to expand...
 
J

jnewl

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

jnewl

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 said:
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
Click to expand...
 

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

Similar Threads

pivot table build using excel macro 0
VBA Pivot Table Error 1
Insert columns and values programmatically with IF statement 9
Creating Pivot Table with Macro using Multiple Sheets 0
Data samples 3
Different Results from the Same Macro 3
macro to copy rows that contain a cell with a certain number 3
Showing/Hiding Pivot-Items 2

Top