+ Reply to Thread
Results 1 to 4 of 4

Macro Not Working Because of Pivot Reference

  1. #1
    Registered User
    Join Date
    03-17-2020
    Location
    USA
    MS-Off Ver
    2019
    Posts
    2

    Macro Not Working Because of Pivot Reference

    Hello, I have a macro that is failing to run. It has been running well daily for a few months, but today I received an error. The first step of the macro is to go to a separate tab and refresh a pivot table and then copy it.

    The error is occurring at the copy step, just after refreshing. The copy is referencing the first item number at the top of the pivot (I highlighted it in red below), but today that item has changed, as our inventory changes daily. There is no guarantee that the first item number is always the same. Is there a way to generically reference the pivot, without an item number? Maybe with a cell reference?

    This is the macro code:

    Sheets("IRMS Available Pivot").Select
    Range("A7").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'0052507'", xlDataAndLabel _
    + xlFirstRow, True

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Macro Not Working Because of Pivot Reference

    If you are referencing a field, it means you want to use it .. no ?
    What do you want to do with your Pivot Table ?
    - Battle without fear gives no glory - Just try

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Macro Not Working Because of Pivot Reference

    If you want to copy the pivot table, you can use ActiveSheet.PivotTables("PivotTable1").Tablerange2.copy. If you don't want any page/filter fields included, use TableRange1 instead.
    Rory

  4. #4
    Registered User
    Join Date
    03-17-2020
    Location
    USA
    MS-Off Ver
    2019
    Posts
    2

    Re: Macro Not Working Because of Pivot Reference

    Rorya,

    Thank you very much, this worked perfectly.

    ActiveSheet.PivotTables("PivotTable1").Tablerange1.copy

+ 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. [SOLVED] Reference to second pivot table not working
    By chough10 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-25-2020, 12:39 PM
  2. Macro Pivot Table Not Working for Certain Computer
    By BolognaMan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-26-2019, 11:55 AM
  3. Macro with activesheet reference stops working when worksheet is renamed
    By Naz555 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-20-2017, 01:31 AM
  4. [SOLVED] Change Year in Macro with reference to cell in Working code.
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-17-2013, 02:11 PM
  5. Macro to filter Pivot not working
    By Startrekfive in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-07-2011, 04:20 AM
  6. Pivot Table macro reference
    By Street3 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-08-2010, 09:25 AM
  7. Macro for Pivot table not working properly
    By oakman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-27-2007, 09:46 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