+ Reply to Thread
Results 1 to 3 of 3

variable source for a pivot !!

  1. #1
    Registered User
    Join Date
    08-23-2013
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    22

    variable source for a pivot !!

    Hi

    I have 30 sheets of data each named after a region and need to make a pivot for each in a silimar layout

    I create a temp file, copy the data into the tem, do the pivot, delete the temp and repeat

    However, the source data for the pivot is the TEMP file

    Need it to be the original file so

    if summaryname is the variable name that loops around (region 1,2,3 etc)... can I replace TEMP1 with the variable summaryname?

    Thanks

    TC


    Please Login or Register  to view this content.

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

    Re: variable source for a pivot !!

    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

  3. #3
    Registered User
    Join Date
    08-23-2013
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: variable source for a pivot !!

    I have tried as below and getting Run Time Error '91' : Object Variable or With Block not Set

    Help !!

    Thanks a Million

    TC

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "'" & summaryname & "!" & "R1C1:R5000C12").CreatePivotTable TableDestination:= _
    "'[test for autmation APE REPORTS 30 9 2013.xls]TEMP2'!R1C1:R21C10", TableName:="PivotTable1", _
    DefaultVersion:=xlPivotTableVersion10
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Change Pivot Source Data in multiple pivot tables
    By jacol in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 05-20-2014, 06:18 AM
  2. Trying to get a Pivot Table to work with variable source data
    By nakedbamboo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-27-2012, 04:04 AM
  3. [SOLVED] Pivot table source range variable
    By tone640 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-15-2012, 05:58 AM
  4. Replies: 2
    Last Post: 04-16-2012, 05:06 PM
  5. Replies: 3
    Last Post: 02-27-2012, 08:03 PM

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