+ Reply to Thread
Results 1 to 16 of 16

GetPivotData

  1. #1
    Registered User
    Join Date
    12-09-2008
    Location
    Canada
    Posts
    79

    GetPivotData

    Hi guys, I've been programming for a while, but strangely never learned to use Pivot Tables. Recently I discovered how much work they can save me so I started using them. I'm still having trouble with the code though. Please take a look and tell me what I'm doing wrong:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by GeorgY; 12-02-2009 at 02:16 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: GetPivotData

    Would probably help if you outlined exactly what was going on...

    Seems as though you're trying to retrieve a value using the GETPIVOTDATA function based on a field stipulated in A2 - but based on your code you're clearing the sheet before doing anything... I would have expected the insertion of Forecast to fail on that basis.

    Please Login or Register  to view this content.
    the above shouldn't work based on the Cells.Clear statement but is pseudo-code showing how to update values etc without need for .Select etc

    Post back with a sample file if you can and/or additional info.

    (that being said I confess I've never actually tried using the GETPIVOTDATA function from VBA... so not sure if the above would work or not...see: http://msdn.microsoft.com/en-us/libr...ffice.11).aspx)
    Last edited by DonkeyOte; 12-02-2009 at 08:14 AM.

  3. #3
    Registered User
    Join Date
    12-09-2008
    Location
    Canada
    Posts
    79

    Re: GetPivotData

    I must have made a mistake pasting the code. Here is the full sub:

    Please Login or Register  to view this content.
    I tried writing it the way you have it, but still doesn't work. I get an "Application defined or object defined error."

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,064

    Re: GetPivotData

    Your syntax for GetPivotdata is still wrong. The first argument should be the name of the data field, the second the name of the column/row field and then a value from that field.
    Also, FYI, if you want to refer to the range a pivot table occupies, you can just use its TableRange1 or TableRange2 properties.
    Everyone who confuses correlation and causation ends up dead.

  5. #5
    Registered User
    Join Date
    12-09-2008
    Location
    Canada
    Posts
    79

    Re: GetPivotData

    Quote Originally Posted by romperstomper View Post
    Your syntax for GetPivotdata is still wrong. The first argument should be the name of the data field, the second the name of the column/row field and then a value from that field.
    Also, FYI, if you want to refer to the range a pivot table occupies, you can just use its TableRange1 or TableRange2 properties.
    Can you help me out with this syntax. I think I understand what you are saying, but I am not sure.

    Basically, I want to look up cell ("A2") in range "invPTrng"
    It will be located in column "FLCCODE" and it will be under the "ATLANTIC" data field.

  6. #6
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,064

    Re: GetPivotData

    It should be:
    Please Login or Register  to view this content.
    if I understood correctly.

  7. #7
    Registered User
    Join Date
    12-09-2008
    Location
    Canada
    Posts
    79

    Re: GetPivotData

    Quote Originally Posted by romperstomper View Post
    It should be:
    Please Login or Register  to view this content.
    if I understood correctly.
    It doesn't work. Didn't you forget to put the data range in - "InvPT?"

    Just to make it simpler:

    I am looking for the number: "20641" which is going to be located in range "invPT" in the first column A

    When it's found, I want the get the number corresponding to it under the "ATLANTIC" data field (column B).

    Does that make it clearer?

  8. #8
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,064

    Re: GetPivotData

    That's what that should do - you don't need the range since you are using the GetPivotData method of the pivottable. Perhaps you need text:
    Please Login or Register  to view this content.
    if that doesn't work, can you post a sample workbook?

  9. #9
    Registered User
    Join Date
    12-09-2008
    Location
    Canada
    Posts
    79

    Re: GetPivotData

    It doesn't work. Please take a look at the WB that I've attached to my 1st post..

  10. #10
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,064

    Re: GetPivotData

    I would but there isn't one there...

  11. #11
    Registered User
    Join Date
    12-09-2008
    Location
    Canada
    Posts
    79

    Re: GetPivotData

    Quote Originally Posted by romperstomper View Post
    I would but there isn't one there...
    I'm trying..the upload keeps failing

  12. #12
    Registered User
    Join Date
    12-09-2008
    Location
    Canada
    Posts
    79

    Re: GetPivotData

    Quote Originally Posted by romperstomper View Post
    I would but there isn't one there...
    OK, it's there now. Just don't refresh the PT because I had to take out the source data to make it smaller.

  13. #13
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,064

    Re: GetPivotData

    OK. Which version of Excel are you using?

  14. #14
    Registered User
    Join Date
    12-09-2008
    Location
    Canada
    Posts
    79

    Re: GetPivotData

    Quote Originally Posted by romperstomper View Post
    OK. Which version of Excel are you using?
    Excel 2000

  15. #15
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,064

    Re: GetPivotData

    The problem is that ATLANTIC is not the name of the data field - it's an item in the AREA column. The syntax you need is:
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    12-09-2008
    Location
    Canada
    Posts
    79

    Re: GetPivotData

    Oh man, you have no idea how frustrated I was getting with this. Pivot tables and I don't get along, yet.

    Thanks a lot!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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