+ Reply to Thread
Results 1 to 3 of 3

I get Run Time Error 5 on a recorded Pivot VBA Macros

  1. #1
    Registered User
    Join Date
    03-11-2021
    Location
    Hyderabad
    MS-Off Ver
    2019
    Posts
    12

    I get Run Time Error 5 on a recorded Pivot VBA Macros

    Sub Open_Projects_pivots()

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Open_Project_Details", Version:=6).CreatePivotTable TableDestination:= _
    "Open Projects pivots!R1C1", TableName:="PivotTable1", DefaultVersion:=6
    Sheets("Open Projects pivots").Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("PivotTable1")
    .ColumnGrand = True
    .HasAutoFormat = True
    .DisplayErrorString = False
    .DisplayNullString = True
    .EnableDrilldown = True
    .ErrorString = ""
    .MergeLabels = False
    .NullString = ""
    .PageFieldOrder = 2
    .PageFieldWrapCount = 0
    .PreserveFormatting = True
    .RowGrand = True
    .SaveData = True
    .PrintTitles = False
    .RepeatItemsOnEachPrintedPage = True
    .TotalsAnnotation = False
    .CompactRowIndent = 1
    .InGridDropZones = False
    .DisplayFieldCaptions = True
    .DisplayMemberPropertyTooltips = False
    .DisplayContextTooltips = True
    .ShowDrillIndicators = True
    .PrintDrillIndicators = False
    .AllowMultipleFilters = False
    .SortUsingCustomLists = True
    .FieldListSortAscending = False
    .ShowValuesRow = False
    .CalculatedMembersInFilters = False
    .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotCache
    .RefreshOnFileOpen = False
    .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")
    .Orientation = xlColumnField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Labels")
    .Orientation = xlRowField
    .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("ShortId"), "Count of ShortId", xlCount
    Range("A1:C4").Select
    Selection.Copy
    Range("A15").Select
    ActiveSheet.Paste
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Labels").Orientation = _
    xlHidden
    With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
    "Requester Team (string)")
    .Orientation = xlRowField
    .Position = 1
    End With
    End Sub


    I am getting run time error 5 on this code and unsure what the error is. This is a recorded macros. FYI "Open_Project_Details" is a dynamic table in the sheet "Open Project Details" "Open Projects pivots" is the destination sheet and will always remain and this sheet will have a button click for this macros

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,058

    Re: I get Run Time Error 5 on a recorded Pivot VBA Macros

    Try adding single quotes to the table destination argument due to the spaces in the sheet name:

    Please Login or Register  to view this content.
    You also need to use code tags when posting code please.
    Rory
    I drink, and I know things

  3. #3
    Registered User
    Join Date
    03-11-2021
    Location
    Hyderabad
    MS-Off Ver
    2019
    Posts
    12

    Re: I get Run Time Error 5 on a recorded Pivot VBA Macros

    You were just Awesome, it worked....

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Recorded Macro returns Run-time error '1004'
    By absconditus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-05-2015, 04:56 AM
  2. Recorded pivot macro run time error 5
    By cindywylie in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-18-2014, 08:38 PM
  3. [SOLVED] URGENT.. Recorded Pivot Table Macro error
    By nawGo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2014, 11:14 AM
  4. Cannot Publish Excel Pivot Chart - Run Time Error
    By crisb184 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-11-2013, 07:04 AM
  5. [SOLVED] Recorded Pivot Table Macro Doesn't Do What I Recorded!
    By diakonos1984 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-26-2013, 11:01 AM
  6. [SOLVED] Error in a recorded macro to create Pivot Table
    By aadeshgandhi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-21-2012, 12:40 PM
  7. Recorded Macros
    By ravichander in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-06-2008, 05:44 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1