+ Reply to Thread
Results 1 to 2 of 2

Reference to second pivot table not working

  1. #1
    Registered User
    Join Date
    12-28-2018
    Location
    Pennsylvania, US
    MS-Off Ver
    Excel 2016-Windows 10
    Posts
    35

    Reference to second pivot table not working

    I'm very new to VBA but I know how to record macros and I tried doing that for a report I run monthly. In the report, I make a pivot table of some data to sum up the data by country. I then highlight the pivot table and paste it below the current pivot table to create a second pivot table and go from there summing up the data by customer and adding in a whole bunch of other stuff. The macro keeps getting hung up on the first reference to the new pivot table when I run it and I'm not sure why. I tried changing the activesheet.pivottables part to sheets("sheet1") to actually reference the sheet I'm working in as some post online recommended but it still got hung up.

    Below is the code where it's getting stuck. I'm assuming the issue is that it can't find pivottable2 for some reason but I don't know why or how to name a pivot table in the middle of code much less how to change all the following code after this section that refers to it as ActiveSheet.PivotTables("PivotTable2"). Any help is appreciated

    Range("A1:E11").Select
    Selection.Copy
    Range("A18").Select
    ActiveSheet.Paste
    With Sheets("Sheet1").PivotTables("PivotTable2").PivotFields("Customer") <----- highlights this line as the issue.
    .Orientation = xlRowField
    .Position = 1
    End With

    NEVERMIND I JUST DISCOVERED FOR SOME REASON THE PASTED PIVOT TABLE IS LABELED PIVOTTABLE3 FOR SOME REASON. I WOULD DELETE THIS IF I COULD.
    Last edited by chough10; 01-24-2020 at 03:54 PM.

  2. #2
    Forum Contributor
    Join Date
    07-01-2018
    Location
    Adelaide, South Australia
    MS-Off Ver
    Office 365, & Excel 2016 on windows 10, & 14.7 for mac, & Excel 2015 for mac
    Posts
    173

    Re: Reference to second pivot table not working

    If your problem is solved, go to "thread tools" at the top of the page, and select "mark thread as solved"

    Also, when you post code, please surround it with CODE tags
    Please Login or Register  to view this content.
    You can get those by clicking on the # button in the tool bar.

+ 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. Indirect reference to a table is not working!
    By Hooty in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-20-2018, 06:59 PM
  2. Replies: 1
    Last Post: 03-23-2017, 03:53 PM
  3. VBA Table reference if working with a copy of the table
    By Vegiepie2016 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-02-2016, 10:56 AM
  4. Replies: 5
    Last Post: 06-17-2014, 10:16 AM
  5. Pivot Table select all Pivot Items not working as intended
    By rasonline in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-13-2014, 05:22 PM
  6. Replies: 2
    Last Post: 05-24-2013, 03:58 PM
  7. [SOLVED] VBA Code for a pivot table to open database and to reference table current page
    By Pete Straman Straman via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-21-2005, 12:06 AM

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