Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Run time error 1004 PivotTableWizard method of worksheet class failed

Status
Not open for further replies.
vba317

vba317

Programmer
Mar 5, 2009
708
US
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


Code: 
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]
 
Sort by date Sort by votes
Skip,

Here is the recorded code to change the source data. Before I start the line number is 2834 when I include this month's data the new data goes to line 2931.
Code: 
Range("A2").Select
    Sheets("Summary").Select
    Range("C3").Select
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        "DATA!R1C1:R2931C5"
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
 
Upvote 0 Downvote

Code: 
    g0XL.Worksheets("Summary").PivotTableWizard _
       SourceType:=xlDatabase, _
       SourceData:="Database"

    g0XL.Worksheets("Summary").PivotTables(1).PivotCache.Refresh


Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
OK, I tried this and now I am getting a run-time error 1004. Reference is not valid
 
Upvote 0 Downvote
Code: 
g0XL.Worksheets("Summary").PivotTableWizard _
       SourceType:=xlDatabase, _
       SourceData:="Database"
 
Upvote 0 Downvote
and you've already defined [highlight #FCE94F]Database[/highlight] as the source table?

What happens when you select [highlight #FCE94F]Database[/highlight] in the Name Box?

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
This is the code I added.
Now that I added this code I get an error 1004 Method range of object global failed.
The tab in the worksheet is called DATA
When I look at Database it says nothing
I also tried database as an object with the same results

Code: 
Dim Database As Range
Set Database = Range("DATA!R1C1:R2931C5")
 
Upvote 0 Downvote

THAT is not what I asked.

I asked you to find Database in the Name Box.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
Sorry,
I had to insert the name database and add the range. I did that.
Now I get a run-time error 1004
A pivot table report cannot overlap another pivot table report
 
Upvote 0 Downvote

You have TWO pivottables physically close together on the same sheet.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
This is the code that you should be using PRIOR to renaming the PT Source
Code: 
'
           .Names.Add _
                Name:="Database", _
                RefersTo:="='" & SourceRange.Parent.Name & "'!" & SourceRange.Address


Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote


In fact is you have this code ALONE
Code: 
'
           .Names.Add _
                Name:="[highlight #FCE94F]Database[/highlight]", _
                RefersTo:="='" & SourceRange.Parent.Name & "'!" & SourceRange.Address

AND have the PivotTable Source Data referencing Database, then that is all you need. You will not need to reassign the PT source data in your code. I thought I had explained this earlier: 11 Apr 14 16:27 and 11 Apr 14 16:40 with respect to Structured Tables, but the same principle for Named Ranges, like [highlight #FCE94F]Database[/highlight]

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
I am sorry that explanation went over my head. I should have asked for clarification. This is what I have now.

1.If I click on the DATA tab and I do Insert >> Name >> Define and I click on the name database in the refers to box I get =DATA!$A$1:$E$2931 which is what it should be. So the name is defined properly
2. If I comment out the code: goXl.Worksheets("Summary").PivotTableWizard _
' SourceType:=xlDatabase, _
' SourceData:="Database"

The procedure will operate without any errors but the range is not updated.
3. If I add the goXl.Worksheets("Summary").PivotTableWizard _
' SourceType:=xlDatabase, _
' SourceData:="Database"
code back in I get the run-time error 1004
A pivot table report cannot overlap another pivot table report

I appreciate your patience on this matter. Is there something else that can be done?

Tom

My current code
Code: 
Public Function xlPivotRefresh(sSheetName As String)
    Dim iBotRow As Integer
    Dim rDataRange As Range
    Dim Database As Object
    Dim SourceWorksheet As Worksheet
    Dim SourceRange As Object
    
    With goXl.ActiveWorkbook
        Set SourceRange = Worksheets("DATA").Range("A1").CurrentRegion
        .Names.Add _
                Name:="Database", _
                RefersTo:="='" & SourceRange.Parent.Name & "'!" & SourceRange.Address

        '        goXl.Worksheets("Summary").PivotTableWizard _
                 '                SourceType:=xlDatabase, _
                 '                SourceData:="Database"
        '
        goXl.Worksheets("Summary").PivotTables(1).PivotCache.Refresh
    End With

   
    With goXl
    'Remove Comand Bars
    .CommandBars("PivotTable").Visible = False
    'Remove Pivottable Field List
    'Doesnt work
    .ActiveWorkbook.ShowPivotTableFieldList = False
    
    End With
End Function
 
Upvote 0 Downvote

1.If I click on the DATA tab and I do Insert >> Name >> Define and I click on the name database in the refers to box I get =DATA!$A$1:$E$2931 which is what it should be. So the name is defined properly
Click to expand...

This is what this code does instead of your doing it manually, assuming that SourceRange references the table range!
Code: 
'
           .Names.Add _
                Name:="Database", _
                RefersTo:="='" & SourceRange.Parent.Name & "'!" & SourceRange.Address
...of course with the proper . reference that was in your code

A pivot table report cannot overlap another pivot table report
Click to expand...
As I explained, you have MULTIPLE pivottables on your sheet! Get rid of the other pivottables or move the other pivottables to the right an appropriate number of columns.

And as previously stated, reference ALL your Excel objects properly!
Code: 
    With goXl.ActiveWorkbook
        Set SourceRange = [highlight #FCE94F].[/highlight]Worksheets("DATA").Range("A1").CurrentRegion
        .Names.Add _
                Name:="Database", _
                RefersTo:="='" & SourceRange.Parent.Name & "'!" & SourceRange.Address

        goXl.Worksheets("Summary").PivotTables(1).PivotCache.Refresh
    End With
I personally would not use ActiveWorkbook. I'd much rather use a SPECIFIC workbook reference, unless the routine is designed to act on a number of workbooks, and then I'd be apt to use some reference other than Active...

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
OK, on this particular workbook I only have this one pivot table so I have changed the reference. I tried to get rid of the activeworkbook but I kept on getting error 1004 so I thought I would activate it than select the specific sheet I was interested in to make sure I was referring to the correct sheet. So currently I am not getting any errors but my pivot table still is not refreshing. If you think the issue is multiple pivot tables how do I get rid of the other pivot tables?

Code: 
Public Function xlPivotRefresh(sSheetName As String)
    Dim iBotRow As Integer
    Dim rDataRange As Range
    Dim Database As Object
    Dim SourceWorksheet As Worksheet
    Dim SourceRange As Object
    
    goXl.Worksheets("Summary").Activate
    goXl.Worksheets("Summary").Select
    With goXl.ActiveWorkbook
        Set SourceRange = .Worksheets("DATA").Range("A1").CurrentRegion
        .Names.Add _
                Name:="Database", _
                RefersTo:="='" & SourceRange.Parent.Name & "'!" & SourceRange.Address

                goXl.Worksheets("Summary").PivotTableWizard _
                                 SourceType:=xlDatabase, _
                                 SourceData:="Database"
        
        goXl.Worksheets("Summary").PivotTables(1).PivotCache.Refresh
    End With

   
    With goXl
    'Remove Comand Bars
    .CommandBars("PivotTable").Visible = False
    'Remove Pivottable Field List
    'Doesnt work
    .ActiveWorkbook.ShowPivotTableFieldList = False
    
    End With
End Function
 
Upvote 0 Downvote
This will indicate the number of PivotTables on the active sheet...
Code: 
Debug.Print ActiveSheet.PivotTables.Count


Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
OK I ran that code and the number 1 was returned.
 
Upvote 0 Downvote
Then there is only one pivot table on that sheet.

So, AGAIN, what happens, if you are on the [highlight #FCE94F]Summary sheet[/highlight] and select [highlight #FCE94F]Database[/highlight] in the Name Box?

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
I am on the summary sheet and if I select Database in Define>>Name I get =DATA!$A$1:$E$2931 which is the correct number. If I manually hit pivot table refresh it does not the range still remains at the old number 2854.
 
Upvote 0 Downvote

If I manually hit pivot table refresh it does not[highlight #FCE94F] the range still remains[/highlight]...
Click to expand...

What [highlight #FCE94F]range[/highlight] are you referring to?

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
1K
remeng
remeng
morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
925
morechocolate
morechocolate
feipezi
  • Locked
  • Question
Hoe to fix run time error 2147217900 (80040e14) syntax error in Order By clause 1
Replies
6
Views
1K
Andrzejek
Andrzejek
remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
899
dhookom
dhookom
EliseFreedman
  • Locked
  • Question
booking tool - all day time slots
Replies
3
Views
523
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top