+ Reply to Thread
Results 1 to 2 of 2

Error when creating pivot table based on user date range selection

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Error when creating pivot table based on user date range selection

    Hi, I am getting the error: Run-time error '5' - Invalid procedure call or argument

    at the line: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="WOF" _
    , Version:=6).CreatePivotTable TableDestination:="Who's On 1st!R2C1", _
    TableName:="PivotTable3", DefaultVersion:=6

    of the following code.. Whats happening is on the sheet "FLHearingsMaster" there is a table.. and there is a button to run the macro WOF - Which prompts the user for a start date and then a finish date... it filters the table (Table_FLHearings) by the date range.. (dates are in table column 6) and then moved that data to another sheet named "Who's on 1st Data" where it creates a table named "WOF" then, and this is where its causing the error, I want it to insert the pivot table, based on the table "WOF"...

    does anyone know what I am doing wrong here by chance?



    Sub WOF()
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        'On Error GoTo TryAgain
        Dim strDate1 As String, strDate2 As String
        Dim lDate1 As Long, ldate2 As Long
        strDate1 = InputBox("From Date")
        If strDate1 = vbNullString Then Exit Sub
            If Not IsDate(strDate1) Then
                MsgBox "Non valid date"
                Run "FilterByDateRange"
            End If
        lDate1 = CDate(strDate1)
    ToDate:
        strDate2 = InputBox("To Date")
        If strDate2 = vbNullString Then Exit Sub
            If Not IsDate(strDate2) Then
                MsgBox "Non valid date"
                GoTo ToDate
            End If
        ldate2 = CDate(strDate2)
        Sheets("Who's On 1st Data").Delete
        Sheets("Who's On 1st").Delete
        Worksheets.Add().Name = "Who's On 1st Data"
        Worksheets.Add().Name = "Who's On 1st"
                With Sheets("FLHearingsMaster")
                    .Rows("25:25").EntireRow.Hidden = False
                    .Rows("25:25").Copy Destination:=Sheets("Who's On 1st Data").Rows("1:1")
                    .Rows("25:25").EntireRow.Hidden = True
                    .Range("Table_FLHearings[#All]").AutoFilter Field:=6, Criteria1:=">=" & lDate1, _
                    Operator:=xlAnd, Criteria2:="<=" & ldate2
                    .Columns("P:Y").EntireColumn.Hidden = False
                End With
            Range("Table_FLHearings[#All]").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Who's On 1st Data").Range("A2")
            Sheets("Who's On 1st Data").Select
                With Sheets("Who's On 1st Data")
                    .Columns("A").Delete Shift:=xlToLeft
                    .Range("A1").Delete Shift:=xlToLeft
                    .ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "WOF"
                    .Range("A:O").Columns.AutoFit
                    .Range("P:Y").ColumnWidth = 21
                End With
            Sheets("Who's On 1st").Select
            Range("A2").Select
            ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="WOF" _
                , Version:=6).CreatePivotTable TableDestination:="Who's On 1st!R2C1", _
                TableName:="PivotTable3", DefaultVersion:=6
            Sheets("Who's On 1st").Select
            Cells(2, 1).Select
            ActiveSheet.PivotTables("PivotTable3").Name = "WOF"
            Range("A2").Select
            Sheets("Who's On 1st").PivotTables("WOF").AddDataField Sheets("Who's On 1st").PivotTables( _
                "WOF").PivotFields("Hrng Date"), "Count of Hrng Date", _
                xlCount
                With ActiveSheet.PivotTables("WOF").PivotFields("Hrng Atty")
                    .Orientation = xlRowField
                    .Position = 1
                    With ActiveSheet.PivotTables("WOF").PivotFields("Hrng Date")
                        .Orientation = xlColumnField
                        .Position = 1
                        With ActiveSheet.PivotTables("WOF").PivotFields("County")
                            .Orientation = xlRowField
                            .Position = 2
                        End With
                    End With
                End With
            Rows("1:1").EntireRow.Hidden = True
            ActiveSheet.PivotTables("WOF").PivotFields("Hrng Atty").ShowDetail = False
            ActiveWorkbook.ShowPivotTableFieldList = False
            ActiveSheet.UsedRange.Borders.LineStyle = xlContinuous
            Call WOFFill
            Rows("2:2").NumberFormat = "dddd mm-dd"
            Columns("C:G").EntireColumn.AutoFit
            Cells.Select
            Range("A2").Value = "Hearing Atty  /  Date"
            Range("B3").Select
            ActiveWindow.FreezePanes = True
            Call ClearSlicers
            ActiveWorkbook.Worksheets("FLHearingsMaster").Columns("P:Y").EntireColumn.Hidden = True
            ActiveWindow.ScrollRow = 16
            Range("$B2:$O24").Select
            Worksheets("Who's On 1st").Activate
            ActiveSheet.UsedRange.Select
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Exit Sub
    TryAgain:
        MsgBox "Invalid Date Range"
        Call WOF
    End Sub
    
    Sub WOFFill()
    Dim rngC As Range
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
        With Worksheets("Who's On 1st")
            With .PivotTables("PivotTable2")
                .TableStyle2 = ""
                .TableRange1.Interior.ColorIndex = xlNone
                .TableRange1.Cells.HorizontalAlignment = xlCenter
                .TableRange1.Cells.VerticalAlignment = xlCenter
                .TableRange1.Columns(1).HorizontalAlignment = xlLeft
                With .TableRange1.Rows(2).Cells
                    .Interior.ColorIndex = 1
                    .Font.ColorIndex = 2
                End With
                    With .TableRange1.Rows(lr).Cells
                         .Interior.ColorIndex = 1
                         .Font.ColorIndex = 2
                    End With
                Set rngC = .DataBodyRange
                rngC.Resize(rngC.Rows.Count - 1, rngC.Columns.Count - 1).SpecialCells(xlCellTypeConstants).Interior.Color = 5263615
            End With
        End With
    End Sub

  2. #2
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Re: Error when creating pivot table based on user date range selection

    I was able to figure this out.

+ 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. Filter pivot table based on named range - Error 1004
    By Upset in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-11-2015, 05:42 AM
  2. Dynamic Range, based on user selection, used with FOR LOOP
    By ericbartha in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-01-2015, 05:08 PM
  3. [SOLVED] Creating a Pivot Table based off an ever changing range...
    By Joven76 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 09-17-2014, 10:23 AM
  4. Replies: 9
    Last Post: 02-26-2014, 04:17 PM
  5. Data table based on user selection and index/match
    By pauldaddyadams in forum Excel General
    Replies: 0
    Last Post: 10-19-2012, 07:58 AM
  6. [SOLVED] Pivot Table Error: 'CANNOT GROUP THAT SELECTION'
    By Rhino_dance in forum Excel General
    Replies: 5
    Last Post: 09-20-2012, 11:35 AM
  7. changing range selection for pivot table based on worksheet
    By twhitezell in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-07-2010, 11:49 AM

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