none
Using a macro for a pivot table RRS feed

  • Question

  • Hi guys,

    I have read the question above and the answers provided in this thread. However, I am still struggling to record a Macro template with a Pivot Table. I am using a Mac and Excel 2011. 

    My steps involve the following:

    1) I import a CSV file

    2) I format the cells (time of day into 12:00 am format)

    3) I create a simple pivot table (time of day and the total site visits)

    4)I group the time of day and colour code the total site visits

    Now, this is where I get confused. Usually, I delete all the information so my cells remain empty, but remain with working macros. With the pivot table, I have deleted the pivot table and left it in its original state but nothing has worked yet.

    I have tried importing new data into my Macro template (with a pivot table) but I keep receiving the 1004 error, so I need to debug the code. Now I have no knowledge of VBA but I when I hit "debug", it says I need to delete/change some code (as you have done in the thread above). However, i never deleted any sheets (as the user did above) so, I don't know where I am going wrong.

    Any help would be most welcome. Below is the code that is appearing on my erroneous macro:

    Sub Macro5()
    '
    ' Macro5 Macro
    '

    '
        Range("A8").Select
        Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
        Selection.AutoFill Destination:=Range("A8:A175")
        Range("A8:A175").Select
        Range("A7:B175").Select
        Sheets.Add
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Test 1.csv!R7C1:R175C2", Version:=xlPivotTableVersion14).CreatePivotTable _
            TableDestination:="Sheet1!R3C1", TableName:="PivotTable4", DefaultVersion _
            :=xlPivotTableVersion14
        Sheets("Sheet1").Select
        Cells(3, 1).Select
        ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
            "PivotTable4").PivotFields("Sessions"), "Sum of Sessions", xlSum
        Range("A5").Select
        Selection.Group Start:=True, End:=True, Periods:=Array(False, False, True, _
            False, False, False, False)
        Range("B5:B28").Select
        Selection.FormatConditions.AddColorScale ColorScaleType:=3
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
            xlConditionValueLowestValue
        With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
            .Color = 7039480
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
            xlConditionValuePercentile
        Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
        With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
            .Color = 8711167
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
            xlConditionValueHighestValue
        With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
            .Color = 8109667
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).ScopeType = xlSelectionScope
        Range("H14").Select
        Sheets("Test 1.csv").Select
        Sheets("Test 1.csv").Move Before:=Sheets(1)
        ActiveWindow.SmallScroll Down:=-598
        Range("A6").Select
    End Sub

     

    Thursday, March 19, 2015 9:44 PM

All replies

  • Hi,

    Base on my test in excel 2013, it works fine.

    You said your excel version is excel 2011, there isn’t that version of office.

    >> I keep receiving the 1004 error

    Does it has detail error message?

    >> it says I need to delete/change some code

    Could you provide the screenshot here?

    On the other hand, you may share a sample file on the OneDrive.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, March 20, 2015 9:29 AM
    Moderator