Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > pivot table - works manually not by macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 28
Default 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:=xlDatab ase, 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.x ls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWorkbook.Close


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
GB GB is offline
external usenet poster
  
Posts: 230
Default pivot table - works manually not by macro
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:=xlDatab ase, 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.x ls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWorkbook.Close


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
GB GB is offline
external usenet poster
  
Posts: 230
Default pivot table - works manually not by macro
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:=xlDatab ase, 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.x ls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWorkbook.Close


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 28
Default pivot table - works manually not by macro
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:=xlDatab ase, 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.x ls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWorkbook.Close


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 28
Default pivot table - works manually not by macro
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:=xlDatab ase, 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.x ls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWorkbook.Close


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 28
Default pivot table - works manually not by macro
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:=xlDatab ase, 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.x ls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWorkbook.Close


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table Chart no longer works with Office:MAC 2008 gw1500se Charts and Charting in Excel 4 October 1st 08 02:17 PM
Make manually changed pivot table column headings work for other u katy Excel Worksheet Functions 0 June 17th 08 01:06 AM
Pivot table is not valid in Excel 2000 but works fine in EXCEL 200 Ulrik Loves Horses Excel Discussion (Misc queries) 1 December 18th 06 03:08 PM
VLOOKUP only works on data manually entered. Mike Rogers Excel Discussion (Misc queries) 6 April 25th 06 07:16 PM
vLookUp. Works Manually but not by code. [email protected] Excel Programming 2 December 3rd 04 11:19 PM


All times are GMT +1. The time now is 02:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement