Macro run-time error:5 - Creating a new pivot from a pivot

N

nellesplace

New Member
Joined
Jul 15, 2011
Messages
3
Caveat - I am brand new to posting on this board and quite new to macros. That being said, I have created a macro with part recording and part hand-editing that makes a new pivot table from a pivot table made by another macro.

I ran the macro quite a few times successfully, then I started having any number of errors in the same location.

Code: 
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Addr, Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="CAMPivot (Step 9)!R3C1", TableName:="PivotTable2", DefaultVersion _
        :=xlPivotTableVersion14

The last error was run-time error 5 - invalid call or argument. Previously, I was having a different error, but I read through a bunch of posts and changed my code to include the dim addr as string reference in hopes of being able to run this macro repeatedly. Oddly enough, the macro that records the first pivot table has not had any problems and is in its original format without the dim addr as string section.

Lastly, I changed the table name from PivotTable1 to PivotTable2 and then back again with no success (thinking the 1 would already be taken by the first pivot table in the workbook)...

Here is the entire code:
Code: 
Sub CAMPivot()
'
' CAMPivot Macro
'
'
    Application.ScreenUpdating = False
    Sheets("MasterPivot (Step 8)").Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Found In")
        .Orientation = xlColumnField
        .Position = 1
    End With
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Macros (Steps 9-10)").Select
    Range("J1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("T1:W1").Select
    Selection.Copy
    Range("X1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("X2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-4]>0,1,"""")"
    Range("X2").Copy Range("X2", Cells(Rows.Count, "K").End(xlUp).Offset(0, 13))
    Range("y2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-4]>0,1,"""")"
    Range("y2").Copy Range("y2", Cells(Rows.Count, "K").End(xlUp).Offset(0, 14))
    Range("z2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-4]>0,1,"""")"
    Range("z2").Copy Range("z2", Cells(Rows.Count, "K").End(xlUp).Offset(0, 15))
    Range("aa2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-4]>0,1,"""")"
    Range("aa2").Copy Range("aa2", Cells(Rows.Count, "K").End(xlUp).Offset(0, 16))
    Columns("x:aa").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("t:w").Select
    Selection.Delete Shift:=xlToLeft
    Application.CutCopyMode = False
    Range("J1").Select
    Dim Addr As String
    Dim Sh As Worksheet
    With ActiveSheet
        Addr = "Macros (Steps 9-10)!" & Worksheets("Macros (Steps 9-10)").Range("J1").CurrentRegion.Address(True, True, xlR1C1)
    End With
    Set Sh = Worksheets.Add
    Sh.Name = "CAMPivot (Step 9)"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Addr, Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="CAMPivot (Step 9)!R3C1", TableName:="PivotTable2", DefaultVersion _
        :=xlPivotTableVersion14
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Address")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("City")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("State")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Name")
        .Orientation = xlRowField
        .Position = 4
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Sales Amount"), "Sum of Sales Amount", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("STRYKERGROUPING"), "Count of STRYKERGROUPING", _
        xlCount
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("ALPHASEARCH"), "Count of ALPHASEARCH", xlCount
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("SCIDATA"), "Count of SCIDATA", xlCount
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("ROSTER"), "Count of ROSTER", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "Count of STRYKERGROUPING")
        .Caption = "STRYKER GROUPING"
        .Function = xlMin
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of ALPHASEARCH")
        .Caption = "ALPHA SEARCH"
        .Function = xlMin
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of SCIDATA")
        .Caption = "SCI DATA"
        .Function = xlMin
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of ROSTER")
        .Caption = "ROSTER "
        .Function = xlMin
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Sales Amount")
        .Caption = "2009-2011 Sales"
        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Address").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Name").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales Amount").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Division").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Account No").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Loc ID No").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("City").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("State").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Zip").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("ROSTER").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("ALPHASEARCH").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("SCIDATA").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("STRYKERGROUPING"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    With ActiveSheet.PivotTables("PivotTable1")
        .ColumnGrand = False
        .RowGrand = False
    End With
    ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow
    Sheets(6).Select
    Sheets(6).Name = "CAMPivot (Step 9)"
    Range("A2").Select
    Sheets("CAMPivot (Step 9)").Move After:=Sheets(5)
    With ActiveWorkbook.Sheets("CAMPivot (Step 9)").Tab
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = -0.499984740745262
    End With
    Range("F2").Select
    Selection.FormatConditions.AddIconSetCondition
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .ReverseOrder = False
        .ShowIconOnly = True
        .IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
    End With
    With Selection.FormatConditions(1).IconCriteria(2)
        .Type = xlConditionValueNumber
        .Value = 0
        .Operator = 7
    End With
    With Selection.FormatConditions(1).IconCriteria(3)
        .Type = xlConditionValueNumber
        .Value = 1
        .Operator = 7
    End With
    Selection.FormatConditions(1).ScopeType = xlDataFieldScope
    Range("G2").Select
    Selection.FormatConditions.AddIconSetCondition
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .ReverseOrder = False
        .ShowIconOnly = True
        .IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
    End With
    With Selection.FormatConditions(1).IconCriteria(2)
        .Type = xlConditionValueNumber
        .Value = 0
        .Operator = 7
    End With
    With Selection.FormatConditions(1).IconCriteria(3)
        .Type = xlConditionValueNumber
        .Value = 1
        .Operator = 7
    End With
    Selection.FormatConditions(1).ScopeType = xlDataFieldScope
    Range("H2").Select
    Selection.FormatConditions.AddIconSetCondition
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .ReverseOrder = False
        .ShowIconOnly = True
        .IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
    End With
    With Selection.FormatConditions(1).IconCriteria(2)
        .Type = xlConditionValueNumber
        .Value = 0
        .Operator = 7
    End With
    With Selection.FormatConditions(1).IconCriteria(3)
        .Type = xlConditionValueNumber
        .Value = 1
        .Operator = 7
    End With
    Selection.FormatConditions(1).ScopeType = xlDataFieldScope
    Range("I2").Select
    Selection.FormatConditions.AddIconSetCondition
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .ReverseOrder = False
        .ShowIconOnly = True
        .IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
    End With
    With Selection.FormatConditions(1).IconCriteria(2)
        .Type = xlConditionValueNumber
        .Value = 0
        .Operator = 7
    End With
    With Selection.FormatConditions(1).IconCriteria(3)
        .Type = xlConditionValueNumber
        .Value = 1
        .Operator = 7
    End With
    Selection.FormatConditions(1).ScopeType = xlDataFieldScope
    Range("A2").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Address").AutoSort _
        xlDescending, "STRYKER GROUPING"
        Cells.Select
    Cells.EntireColumn.AutoFit
    ActiveWorkbook.ShowPivotTableFieldList = False
    Range("A2").Select
    ActiveWindow.Zoom = 90
    Application.ScreenUpdating = True
    MsgBox "Proceed to Step 10 on the Macros (Steps 9-10) sheet tab."
    
End Sub

Any help is sooo very much appreciated - I developed this awesome (only partially awesome right now) automated process here at work, and now it's suddenly faulty.

Jenelle
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Still having the same problem... :( I've entered a different variation of the same code, but I just can't get it to run again. (I'm running excel 2010). Any help would be so much appreciated!

Here's the updated code giving me a runtime error -
Code: 
    Range("J1").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Worksheets("Macros (Steps 9-10)").Range("J1").CurrentRegion, Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:="", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion14
 
Upvote 0
Update: I am now receiving run-time errror 1004: pivot table field name is not valid...... (this is with the most recent code above) :eeek:
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Excel VBA Getting error when creating pivot chart but error only occurs after sub that creates pivot tables. No error if directly called
Replies
0
Views
900
Steve48
S
B
  • Solved
Recording macro pivot table error
Replies
2
Views
1K
biglb79
B
S
  • Question Question
Keep getting an "Error 1004: item could not be found in the OLAP cube" when applying the filter to pivot table
Replies
5
Views
1K
RoryA
RoryA
S
  • Question Question
Why won't a macro for a pivot table run on an identical workbook?
Replies
0
Views
762
superbuttz
S
B
  • Solved
VBScript to create pivot for entire data on a tab
Replies
9
Views
3K
Domenic
D

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top