I have tried writing a module that will create a pivot table in a named worksheet with a range of data that changes from day to day. I was able to do with easily in Excel 2003 but when I tried to duplicate that in Excel 2007, it doesn't work. It fails with a Run-time error 5: Invalid procedure call or argument (

Apparently (according to one source I found: http://support.microsoft.com/kb/940166) Microsoft has acknowledged this issue but I don't know whether they have resolved it or not. Anyway, I was wondering if any of the experts in this forum have come across this error and know how to fix it. At the above link's suggestion, I modified the code a little and got it to work but it really doesn't suit my purpose and makes me have to do more work by renaming the worksheet and placing the pivot table in a new location and filling in the Worksheet header to summarize it. I'd like it to create the pivot table in the existing worksheet MTD Summary at the point I tell it to.

Here is my code:

Sub FC_RSCResults_DateNewPivot()
'
' FCCResults_DateNewPivot Macro
'

Dim LR As Long, LC As Long, sourceDataRange As String

LR = Range("A" & Rows.Count).End(xlUp).Row
LC = ActiveSheet.UsedRange.Columns.Count

sourceDataRange = "MTD Data!R1C1:R" & LR & "C" & LC


' Select MTD Data Sheet and Format CreateDate column

Sheets("MTD Data").Select
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "m/d/yyyy"
Range("A1").Select


' Create Pivot Table code below

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
sourceDataRange, Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion12 ' *** THE CODE FAILS HERE with the yellow arrow *** '

'Sheets("Sheet4").Select 'This article says to remove this line: http://support.microsoft.com/kb/940166


Cells(6, 1).Select ' remove this line as well to prevent macro failure on pivot table

With ActiveSheet.PivotTables("PivotTable1").PivotFields ("AccountNumber")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("ContactResult")
.Orientation = xlRowField
.Position = 2
End With

'ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base, SourceData:= _
' sourceDataRange).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
'ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
'ActiveSheet.Cells(3, 1).Select
'ActiveWorkbook.ShowPivotTableFieldList = True

ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("AccountNumber"), "Count of AccountNumber", xlCount
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
ActiveSheet.PivotTables("PivotTable1").DataPivotFi eld.PivotItems( _
"Count of AccountNumber").Caption = "Count of Contact Results"

Range("C7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R54C2"
Range("C7").Select
Selection.AutoFill Destination:=Range("C7:C54")
Range("C7:C54").Select
Selection.Style = "Percent"

Selection.NumberFormat = "0.00%"

Range("C54").Select
Selection.ClearContents

Range(").Select
Selection.ClearContents

Range(").Select


End Sub

Thanks, guys.

Sorry Jake, I have almost no Pivot Table expertise. A sad hole in my toolkit. Hopefully one of the others will...