+ Reply to Thread
Results 1 to 2 of 2

Issue running Macro with Pivot Table

  1. #1
    Registered User
    Join Date
    01-26-2017
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013 - Windows 7
    Posts
    7

    Issue running Macro with Pivot Table

    I made a macro that works fine on my computer, but when my coworker tries to use it, she gets an error message when the macro tries to create a pivot table. It says Invalid Argument

    Receiving error on this line:
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Sheet1!R1C1:R8673C35", Version:=6).CreatePivotTable TableDestination:= _
    "", TableName:="PivotTable1", DefaultVersion:=6


    Does anyone know how to edit the line so that it works on my coworker's computer?




    Full Code listed below


    Sub VoucherAudit()
    '
    ' VoucherAudit Macro
    '

    '
    Selection.AutoFilter
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("I2").Select
    ActiveSheet.Range("$A$1:$AH$16924").AutoFilter Field:=9, Operator:= _
    xlFilterValues, Criteria2:=Array(0, "12/1/2019")
    Range("A1:AH16924").Select
    Range("I2").Activate
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Cells.Select
    Cells.EntireColumn.AutoFit
    Cells.EntireRow.AutoFit
    Range("C14").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 30
    Range("AI1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "SI or PR"
    Range("AI2").Select
    ActiveCell.FormulaR1C1 = _
    "=" & Chr(10) & "IF(LEFT(RC[-2],2)=""PR"",""Promotional""," & Chr(10) & "IF(LEFT(RC[-2],4)=""CSAT"",""Promotional""," & Chr(10) & "IF(LEFT(RC[-2],2)=""MK"",""Promotional""," & Chr(10) & "IF(LEFT(RC[-2],2)=""SI"",""Strategic Investment""," & Chr(10) & """Other""))))"
    Range("AI2").Select
    Selection.AutoFill Destination:=Range("AI2:AI8673")
    Range("AI2:AI8673").Select
    Range("AI2").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Sheet1!R1C1:R8673C35", Version:=6).CreatePivotTable TableDestination:= _
    "", TableName:="PivotTable1", DefaultVersion:=6
    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("SI or PR")
    .Orientation = xlRowField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Geo")
    .Orientation = xlRowField
    .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Geo")
    .Orientation = xlColumnField
    .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Voucher Amount"), "Sum of Voucher Amount", xlSum
    ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("PivotTable1"), _
    "Month (Order Date)", , xlTimeline).Slicers.Add ActiveSheet, , _
    "Month (Order Date)", "Month (Order Date)", 240.75, 482.25, 262.5, 108
    ActiveWorkbook.SlicerCaches("NativeTimeline_Month__Order_Date").TimelineState. _
    SetFilterDateRange "12/1/2019", "12/31/2019"
    End Sub

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Issue running Macro with Pivot Table

    Some comments:
    1 - Add code tags to your code => See forum's rules
    2 - Attach an Excel sample file to help to debug
    3 - Is it the same file with the same number of sheets which is used on the 2 computers ?
    - Battle without fear gives no glory - Just try

+ 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. Running Total Issue in my Pivot Table
    By samshall786 in forum Excel Charting & Pivots
    Replies: 12
    Last Post: 05-11-2022, 08:24 AM
  2. Running a Macro - Pivot Table Error
    By AndreO4 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-21-2019, 07:31 AM
  3. Pivot Table Macro issue.
    By expeo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-01-2018, 08:56 AM
  4. Pivot Table & Chart with Excel VBA - issue on mAcro
    By bsguna in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-14-2016, 06:30 AM
  5. Pivot table not updating after running the macro
    By noobforce123 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-06-2015, 09:46 AM
  6. Running Macro always has error at pivot table creation
    By culverhr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-29-2012, 12:12 PM
  7. [SOLVED] Run time error when running macro to populate a pivot table
    By Pete Straman Straman via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-22-2005, 01:06 PM

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