+ Reply to Thread
Results 1 to 4 of 4

Pivot table not created due to sheet reference

  1. #1
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    273

    Pivot table not created due to sheet reference

    Hello,

    I have the below code to create a pivot table based off of a data set. It is a template that will be used going forward however the code references Sheets("Sheet33").Select in it. The problem with this is when I test and delete tabs and rerun the code the sheet is not Sheet 33 any longer. What can I put to make it dynamic? I have code that creates two pivot tables and they both have this problem.


    Please Login or Register  to view this content.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,355

    Re: Pivot table not created due to sheet reference

    Hi there,

    This proposed solution is untested as I don't have access to your workbook.

    I'm assuming (maybe a BIG assumption!) that the "Sheet32" you're referring to is the name which Excel assigned to the new worksheet when you created your code using the macro recorder.

    See if the following code does what you need - I've tried to tidy up the code generated by the macro recorder:

    Please Login or Register  to view this content.
    The newly-created worksheet will appear as the last worksheet of the workbook.

    You can change the highlighted values to suit your own requirements.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    273

    Re: Pivot table not created due to sheet reference

    Thanks Greg!

    I ended up rebuilding the code with some google research and was able to get it to work. The code is below. I appreciate your help.

    Please Login or Register  to view this content.

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,355

    Re: Pivot table not created due to sheet reference

    Hi again,

    Many thanks for your very prompt feedback and also for the Reputation increase - much appreciated!

    You're welcome - glad I was able to help.

    Best regards,

    Greg M

+ 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. VBA to filter pivot table from a cell reference in another sheet
    By rickettsd96 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 09-22-2017, 05:45 AM
  2. VBA to filter pivot table from a cell reference in another sheet
    By rickettsd96 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-28-2016, 05:06 AM
  3. Replies: 0
    Last Post: 03-16-2015, 12:56 PM
  4. Pivot table to reference a dynamic range on another sheet
    By Chumpalot in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-30-2014, 09:19 AM
  5. Replies: 2
    Last Post: 09-03-2014, 10:51 AM
  6. formula to reference a sheet not created yet
    By ryanstout87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-11-2012, 05:22 PM
  7. Getting Macro to reference newly created sheet
    By creuther in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-27-2011, 01:04 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