+ Reply to Thread
Results 1 to 4 of 4

Difficulty w/Macro to create a Pivot Table w/fields off Any Set of Data

  1. #1
    Registered User
    Join Date
    04-13-2020
    Location
    california
    MS-Off Ver
    2019
    Posts
    3

    Difficulty w/Macro to create a Pivot Table w/fields off Any Set of Data

    Hi

    I am trying to record a macro that takes a set of data, re-formats it in a way I would like, then creates a Pivot table with certain field categories in it.

    The trouble I am having -- I think-- is that I am trying to make this work for any set of data no matter the sheet name. We get this data with different sheet names, but obviously when I record the macro it will record to create the pivot table off a sheet with a specific name.

    How do I change the VBA code so that this macro will work on any sheet it is in, and not only the one named with the macro I set out?

    When bypassing this, if the code makes it to the pivot table, it is unable to add the correct fields to it, giving me run time error 5. Attached are two screenshots of the code. Thank you!

    snip1.PNG
    snip2.PNG

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Difficulty w/Macro to create a Pivot Table w/fields off Any Set of Data

    It would be soooo much easier if you pasted the actual code and not a picture of your code - but basically, you need to capture the range of the data for the activesheet, like

    Please Login or Register  to view this content.
    and then change SourceData to this

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    04-13-2020
    Location
    california
    MS-Off Ver
    2019
    Posts
    3

    Re: Difficulty w/Macro to create a Pivot Table w/fields off Any Set of Data

    Hi, it is still giving me an error 1004


    rngData = ActiveSheet.UsedRange
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "rngData.Address(True, True, xlR1C1, True)", Version:=6).CreatePivotTable _
    TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
    :=6

    This is the portion of the code which I changed and which is highlighted. I set

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Difficulty w/Macro to create a Pivot Table w/fields off Any Set of Data

    No quotes aound the rngData.Address part - you are using a variable that returns a string. And you need to use Set to create the range object

    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. [SOLVED] How to create a Calculated Field based off two Avg fields in a Pivot table?
    By rcocalm in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 02-22-2014, 03:18 AM
  2. Excel 2007 : Macro to add Pivot table Data fields
    By vikrantm26 in forum Excel General
    Replies: 1
    Last Post: 07-09-2010, 08:49 AM
  3. Differentiate between column fields and data fields in a pivot table
    By whiteheadw in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-12-2009, 01:59 PM
  4. [SOLVED] how do i create pivot table from data fields containing forumulas
    By Pam Deshazier, SRHS in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-21-2006, 06:10 PM
  5. [SOLVED] How do pivot table source fields automatically create tabs
    By Datamonkey in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-25-2006, 01:55 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