+ Reply to Thread
Results 1 to 5 of 5

macro for date change in pivot table

  1. #1
    Registered User
    Join Date
    08-22-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    3

    macro for date change in pivot table

    Hi All,

    I'm trying to figure out how to write a macro for changing date in a pivot table.

    I have a pivot table that I update every.

    The pivot table data contains accumulated data from the begining of the month.

    Each day I add data from yesterday, except for Sunday, in which I add data from Thursday (my data never includ Friday and Saturday).

    So if today is Sunday 22/08/2010, I added data for Thursday 19/08/2010
    And I need to change the date 19/08/2010 in my pivote table after refreshing it.

    This is the macro for refreshing the pivot table and chaning the date for 15/08/2010

    Range("E8").Select
    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
    ActiveSheet.PivotTables("PivotTable2").PivotFields("end serv date").CurrentPage _
    = "15/08/2010"

    How do I make it dynamic so the macro will change the date to the next day

    Thanks

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: macro for date change in pivot table

    I have a pivot table that I update every.
    Every WHAT?

    Each day I add data from yesterday, except for Sunday, in which I add data from Thursday (my data never includ Friday and Saturday).

    So if today is Sunday 22/08/2010, I added data for Thursday 19/08/2010
    Why don't you just add the data for the dates that you want to apply for reporting and save yourself a lot of trouble? Just adjust the Thursday/Sunday date for which you want to report and you won't need any macro manipulation at all.

  3. #3
    Registered User
    Join Date
    08-22-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: macro for date change in pivot table

    the date is the parameter that difine the the pivot table
    I need it to be this way becuase I use this data in another excel sheet that display all sort of data from other pivot tables.

    see the pivot table in the attached file, I have to change the date after I refresh the pivot table so I can do vlookup and display the data to the curnt date in another excel sheet.

  4. #4
    Registered User
    Join Date
    08-22-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: macro for date change in pivot table

    the date is the parameter that difine the the pivot table ' I need it to be this way becuase I use this data in another excel sheet that display all sort of data from other pivot tables.

  5. #5
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: macro for date change in pivot table

    see the pivot table in the attached file
    attached where?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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