none
Dynamic Range VBA RRS feed

  • Question

  • Hi team,

     i try to create this on VBA excell

    the source data is other sheet A which has auto refresh ON based on interval time on external resources, so no Human interface on that excell

    so i want to try to make my Pivot IN SHEET b is autoupdate based on that refresh and data from sheet A

        nBook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
            "ALL_DATA!R2C2:R"" & Val(o + Val(AA)) & ""C17").CreatePivotTable TableDestination:= _
            "[Book1]Sheet1!R2C2", TableName:="PivotTable1", DefaultVersion:= _
            xlPivotTableVersion10
        nSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("DEPT", _
            "SECT"), ColumnFields:="TANGGAL"
        nSheet.PivotTables("PivotTable1").PivotFields("TOTAL").Orientation = _
            xlDataField

    I got error on that Line, but if i change "" & Val(o + Val(AA)) & ""  to number like

        nBook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
            "ALL_DATA!R2C2:R11163C17").CreatePivotTable TableDestination:= _
            "[Book1]Sheet1!R2C2", TableName:="PivotTable1", DefaultVersion:= _
            xlPivotTableVersion10
        nSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("DEPT", _
            "SECT"), ColumnFields:="TANGGAL"
        nSheet.PivotTables("PivotTable1").PivotFields("TOTAL").Orientation = _
            xlDataField

    the statement is running properly but it dot not match with the source data

    Thursday, December 5, 2019 3:57 AM

All replies

  • In Val(o + Val(AA)), what is o (lower case letter o)? And what is AA?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, December 5, 2019 8:52 AM