Run-Time Err 1004 Methd Rng of Obj Globl Fail

Microsoft Office excel 2007
November 18, 2010 at 10:58:36
Specs: Windows XP
I am creating a macro to pull a table from a Pivot Table and format it properly, then move to the next entry and do the same. I believe the rest of the Macro is working correctly but I keep getting this error, Run Time Error 1004 Method Range of Object Global Failure. I have looked at several blogs and self help sites and keep almost getting an accurate answer. I'd like to know how to address it, what causes it and how to avoid it in the future, if possible.
My code is below.

Sub OEX()
' OEX Macro
' Keyboard Shortcut: Ctrl+Shift+A
Selection.ShowDetail = True
Range("Table1[[#All],[Site]]").Select < this is causing the issue
Selection.NumberFormat = "General"
Selection.NumberFormat = "General"
Range("Table1[[#All],[AM Commission]]").Select
Selection.NumberFormat = "General"
Sheets("Sheet3").Name = "Ann Atkins"
End Sub

See More: Run-Time Err 1004 Methd Rng of Obj Globl Fail

November 18, 2010 at 19:55:51
First, I'd like to suggest that you read the How To mentioned in my signature line before posting code in this forum.

As far as the error in your code, I don't know much about using Pivot Tables and have never done an VBA coding related to them, but I did play around a bit in an effort to help you out.

This much I can say:

The line that is giving you the error is trying to select a Named Range, specifically the range named "Table1[[#All],[Site]]". You don't have a range with that name in your workbook.

Prove it to yourself by trying to run this line - you'll get the same error:


As far as I can tell the code to format a Pivot Table field is something like this:

   With ActiveSheet.PivotTables("PivotTable4").PivotFields("w")
        .NumberFormat = "General"
    End With

Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •
Related Solutions

Ask Question