GetPivotData for first column?

  • Hello,

    I have a workbook which grabs and transposes raw data from another workbook, then a pivot table re-arranges that data. The data is then linked to another sheet for calculations and graphs are constructed from those calculations. The origin workbook is frequently updated with new data.

    My problem is in trying to automate this process. I have been able to grab the data from the pivot table and put it onto my calculations sheet using GetPivotData. However, in the first column of my pivot table is the date, which corresponds to the other entries. The date column is under "Row labels" in the field list. I'm having trouble figuring out a way to have excel automatically copy all of these dates over to my calculations sheet. GetPivotData doesn't automatically write itself if I hit = and click on a date, presumably because its the first column and not technically in a data field. I also havent been able to manually write a version of it that gives me anything other than #REF or #VALUE.

    Is there a way to solve this problem using GetPivotData or some other method? It doesn't seem like it should be complicated but it's causing me a lot of problems.

  • Re: GetPivotData for first column?

    If you are automating it in code anyway, why not have your code copy the first column of the pivot table's TableRange1 property?

    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: GetPivotData for first column?

    I'm not using code, I don't really know how to use VBA in excel. Is there a formula or something that I can use to grab the range of a column from a pivot table?

  • Re: GetPivotData for first column?

    Excel 2010 PivotTable, Tables
    Get data from the PT with GetPivotData() from the data field
    and with Index() from the row/column fields.
    Row/column fields assigned dynamic Defined Names.
    Optional macro to change Table size in sync with PT.
    Advanced level.
    If you get *.zip, don't unzip, just rename *.xlsm

  • Re: GetPivotData for first column?

    That sort of works, but I don't think i've been too clear in stating what I want.

    I need to be able to have any data in certain columns of the pivot table copied to another sheet for calculations. The pivot table grow larger in rows so I can't specify a static range.

    If I use index or link the entire column then I end up with a lot of zero data hanging off of the end which tends to upset my graphing. However I may be using the Index function wrong, is there a way to specify a dynamic range for it?

    From what i've seen so far I either need a function or macro that can be updated/refreshed when the pivot table gains more data and fill out more rows accordingly. Alternatively if there is a way to make any cell with the date 0/01/00 blank but still have a formula in it so that it will update when new data is entered and not interfere with the graphs then that would also work. (i.e. cell is blank but formula bar isn't...something like conditional formatting but I can't see anything that will remove text in a cell.)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!