Run time error 1004 PivotTableWizard method of worksheet class failed

[vba317]

I have written a module that gives me the run time error 1004. I am not sure which one of my variables is incorrect.
Thanks for any help provided Tom

The error is highlighted in Blue
The worksheet Has two tabs on it.
Tab1 is called Summary
Tab2 is called Data
The Worksheet Name is AHS_Cnts
The columns that have data in them are A - E on tab DATA
Currently the last row is 2931

Public Function xlPivotRefresh(sSheetName As String)
    Dim iBotRow As Integer
    Dim sDataArea As String
    Dim pvt As PivotTable
'    For Each pvt In ActiveSheet.PivotTables
'        MsgBox pvt.Name & ":" & pvt.TableRange1
'
    'Count Last Row of DATA Sheet
    Call xlCalcBotRow(iBotRow)
    sDataArea = "DATA!$A$1" & ":" & "$E$" & iBotRow
    'sSheetName = "DATA"
    With goXl.ActiveWorkbook
        .Worksheets(sSheetName).Activate
    End With
    'Rename Pivot Table Data with new range
    'C5 means column 5 of the pivot table
    ActiveWorkbook.Names.Add Name:="PivotData", RefersToR1C1:="=DATA!R1C1:R" & iBotRow & "C5"
    With goXl.ActiveSheet
        .PivotTables("PivotTable1").RefreshTable
    End With
    'Rename wizard range
    With goXl.ActiveSheet
[Blue]  .PivotTableWizard SourceType:=xlDatabase, SourceData:="DATA!R1C1:R" & iBotRow & "C5", TableDestination:="Summary", TableName:="PivotTable1" [\Blue]
        .PivotTables("PivotTable1").PivotCache.Refresh
    End With
    'Next
    'Remove Pivottable Field List
    ActiveWorkbook.ShowPivotTableFieldList = False
End Function


[\code]

 

[vba317]

Database refers to DATA!$A$1:$E$2931

 

[SkipVought]

???

If I manually hit pivot table refresh it does not [highlight #FCE94F]the range still remains at the old number 2854[/highlight].

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[vba317]

I know this does not make any sense to me either. I don't know if this matters I will go through all the steps:
1. I am on the summary tab
2. Insert >>Name>>Define
3. The define name box comes up
4. Names in workbook is blank Refers to =Summary!$A$3
5. I click on the only name available: Database
6. The refers to column =DATA!$A$1:$E$2931
7. When I right click on wizard and I click on back the range is defined as database.

I am thinking now I might have to create a new excel spreadsheet from scratch. Because the ranges look right but yet the individual fields are not updating.