Question about Dynamic Range for Pivot Tables - Excel 2007
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.
|