+ Reply to Thread
Results 1 to 2 of 2

Creating pivot table to Existing worksheet

  1. #1
    Registered User
    Join Date
    12-12-2005
    Location
    USA
    MS-Off Ver
    2007 with XP Pro
    Posts
    25

    Creating pivot table to Existing worksheet

    Here's all the relevant information:

    a) I was able to successfully create a pivot table, in a newly created tab named "Sheet2". This pivot table is contained in A:E, was copied and the duplicate of that pivot table pasted into I:M.

    b) Next I attempted to generate a new pivot table, using a new data range, to the "Existing worksheet" at P:V. When running the VBC code for the new pivot table I get the error "Run Time 1004: Unable to get the Pivot Table Property of the Worksheet class"

    The broken VBA code is below:
    Sheets("Raw Data").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "'Raw Data'!R2C1:R" & RowCount & "C54").CreatePivotTable TableDestination:= _
    "'[MBR Prep Work 1.1.xls]Sheet2'!R1C16", TableName:="PivotTable3", _
    DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:=Array( _
    "Column1", "Column2", "Column3", "Column4", "Column5"), ColumnFields:= _
    "Month"
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Case No").Orientation = _
    xlDataField
    Also, as a kinda-related question: Using VBA, can one create a new pivot table using the data range of the previous / original pivot table. The manual option that says something about "saving memory and making workbook the smaller"? If so, can you point me in the direction of an example somewhere?

    Any help would be greatly appreciated!!!

  2. #2
    Registered User
    Join Date
    12-12-2005
    Location
    USA
    MS-Off Ver
    2007 with XP Pro
    Posts
    25
    I wonder if the target worksheet must be actually named, vs the default Sheet1, Sheet2, Sheet3, etc etc

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "'Raw Data'!R2C1:R" & RowCount & "C54").CreatePivotTable TableDestination:= _
    [MBR Prep Work 1.1.xls]Sheet2'!R1C16
    Would that actually make a difference?

+ 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. Excel 2007 : Complex query on a pivot table help
    By ZKL in forum Excel General
    Replies: 0
    Last Post: 07-15-2008, 10:26 AM
  2. Repeat Pivot Table Page Fields in a 2nd Worksheet
    By penfold in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-13-2008, 12:12 PM
  3. Extending Data Range of a Pivot Table
    By Rabi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-28-2007, 06:14 AM
  4. Pivot Table from MS Query
    By Jim Palmer in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-17-2007, 09:42 AM
  5. Pivot table and totals calculation of columns
    By mb_user in forum Excel General
    Replies: 1
    Last Post: 08-10-2007, 02:18 AM

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