Skip to content

Instantly share code, notes, and snippets.

@mynameispj

mynameispj/gist:8963231

Created February 12, 2014 19:53
Download ZIP

Revisions

  1. mynameispj created this gist Feb 12, 2014.
    78 changes: 78 additions & 0 deletions gistfile1.vb
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,78 @@
    Sub MyAdd(ByVal strTableName As String, ByRef arrData As Variant)
    Dim Tbl As ListObject
    Dim NewRow As ListRow

    Set Tbl = Worksheets("Cart Conversion").ListObjects(strTableName)
    Set NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)

    ' Handle Arrays and Ranges
    If TypeName(arrData) = "Range" Then
    NewRow.Range = arrData.Value
    Else
    NewRow.Range = arrData
    End If
    End Sub

    Sub Macro3()

    ' ActiveSheet.PivotTables("PivotTable3").PivotFields("Week Number").PivotItems("4").LabelRange.Interior.Color = vbYellow

    ' ActiveSheet.PivotTables("PivotTable3").PivotFields("Week Number").PivotItems("4").PivotItems("Category Tiles Widget").DataRange.Interior.Color = vbYellow

    ' Dim PvtTbl As PivotTable
    ' Dim rng1 As Range
    ' Dim rng2 As Range

    ' Set PvtTbl = Worksheets("Cart Conversion").PivotTables("PivotTable3")
    ' Set rng1 = PvtTbl.PivotFields("OPS").DataRange
    ' Set rng2 = PvtTbl.PivotFields("Week Number").PivotItems("4").DataRange.EntireRow

    ' Intersect(rng1, rng2).Interior.Color = vbYellow

    ' Worksheets("Cart Conversion").PivotTables("PivotTable3").PivotFields("OPSTEST").DataRange.Interior.Color = vbYellow


    For Each Pi In Worksheets("Cart Conversion").PivotTables("PivotTable3").PivotFields("Week Number").PivotItems
    'Do something you want here, for example, choose them, or list them

    If Pi.Name = "4" Then

    For Each labelName In Pi.LabelRange
    ' q = q + cell.Value

    If labelName.Value = "Hero Slideshow" Then

    Dim test(1) As String
    Dim myVar As String

    myVar = labelName.Offset(0, 2).Text

    test(0) = "4"
    test(1) = myVar

    ' MsgBox test(0)
    ' MsgBox test(1)



    End If

    Next labelName

    ' For Each cell In Pi.DataRange
    ' q = q + cell.Value
    ' cell.Interior.Color = vbWhite
    ' Next cell
    ' MsgBox q
    End If

    ' MsgBox ltvItm.Value

    Next


    ' Worksheets("Cart Conversion").PivotTables("PivotTable3").PivotFields("Week Number").PivotItems("4").DataRange.EntireRow.Interior.Color = vbWhite

    ' Worksheets("Cart Conversion").PivotTables("PivotTable3").PivotFields("Week Number").PivotItems("4").DataRange.Interior.Color = vbYellow

    End Sub