+ Reply to Thread
Results 1 to 6 of 6

Selecting Pivottable SourceData

  1. #1
    Registered User
    Join Date
    09-06-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Selecting Pivottable SourceData

    I am trying to write code to automatically select the Source Data for a pivot table (the Source Data is in an Excel worksheet).

    I used: Let MySource = ActiveCell.PivotTable.SourceData

    to get "Data!R4C1:R792C99"

    I cannot for the life of me now figure out how to *select* that worksheet and range. I have tried parsing the string so I can use the form:

    Sheets("Data").Select
    Range(Cells(4,1),Cells(792,99)).Select

    It works, but seems an unnecessary bit of coding to get there and I have now found I cannot figure out how to handle a Sheet Name which includes spaces.... grrr...

    I am sure there must be an easier way, but I'm pretty new at this (first post!)

    Thank you for any advice.

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Selecting Pivottable SourceData

    Hi, Bill@,

    try it like this for the source (assuming there are no values above row 4 in your sheet) when building the pivot table (this sniplet is from code for 2007/2010 but should work for 2003 as well):

    Please Login or Register  to view this content.
    HTH
    Holger

  3. #3
    Registered User
    Join Date
    09-06-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Selecting Pivottable SourceData

    Thank you, Holger. I probably wasn't very clear in my question. I am starting from an existing pivot table and want the macro to then go to and select the data in the worksheet which is the source for the pivot table.

    Thanks!

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Selecting Pivottable SourceData

    Hi, Bill@,

    right now it seems Iīm too dumb to figure out why you want to select the Source (I havenīt found needs up to now as to do so in my pivots - maybe because of the use of mainly 2007 and 2010).

    To find the range as well as the sheet:
    Please Login or Register  to view this content.
    If there is more than one area use an array and loop through that array.

    BTW: if you are using blanks in the sheetname, use "'1 - Data'" instead of "1 - Data".

    CIao,
    Holger

  5. #5
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Selecting Pivottable SourceData

    perhaps
    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  6. #6
    Registered User
    Join Date
    09-06-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Selecting Pivottable SourceData

    Ah! That 'ConvertFormula' will work out fine. For some reason, not able to get the code to work directly, but now can I easily parse it into Worksheet and Range values.

    Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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