News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • VBA Macro Pivot Table Query

    Topic Resolution: Resolved

    Tagged: ,

    This topic contains 13 replies, has 3 voices, and was last updated by  debaser 6 months, 1 week ago.

    • Author
      Posts
    • #348625 Reply

      Kirsty
      Da Boss

      In a pivot table, I had created macros to be able to extract data for the last month’s entries, and another to clear that selection, in order to be able to print reports. Not a problem…

      I now need another macro, to be able to select data from the last entries too, selected by the date. These will be the bottom rows of the source data (the most recent), and I’m not sure the best way to achieve this.

      One idea I had was to add a date selector in a macro input box to be able to select the necessary data, but I’ve not done much with macros recently, so that might not be the most effective and efficient way.
      If someone had a suitable suggestion, I’d love to hear it, thanks.

    • #348634 Reply

      debaser
      AskWoody_MVP

      Do you mean you are filtering the pivot table, or trying to extract the source data?

    • #348638 Reply

      Lugh
      AskWoody_MVP

      Kirsty, is your source data within the same workbook, in another workbook, in a DB like Access, or elsewhere?

      PS which version of Excel?

      Lugh.
      ~
      Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
      i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

      • #348809 Reply

        Kirsty
        Da Boss

        Thx for your replies @lugh & @debaser!

        a) it’s being created in Excel 2013, and it also gets used on Office for Mac (2012?);
        b) the source data is on a different worksheet in the same workbook;
        c) the macro has to be used (in a button) by a non-techie, so the macro is to automate the results – filtering isn’t an option, as the reports could give inaccurate results (this is a compliance report), nor is starting a new pivot table report each time (the users don’t understand what a pivot table is!);
        d) selecting last month’s data extraction was easier to set up, than selecting all the entries for the last entry date in the source data… that’s where my conundrum lies – the selection of entries in the source data (I’m pre-thinking this, as it needs to be ready in 7-10 days and I’ve not had time to play with it yet).

        Thanks again 🙂

    • #348642 Reply

      Lugh
      AskWoody_MVP

      In a pivot table, I had created macros to be able to extract data for the last month’s entries, and another to clear that selection

      I’ve no idea of your expertise level, so a couple of basics:

      1. A pivot can automatically provide columns for Year, Quarter and Month based on date data—this will provide whichever date view you want.

      2. When I need a different view of the same data, I simply create a new sheet with a new pivot in it. Much simpler than swapping back n forth between diff views.

      select data from the last entries too, selected by the date

      3. Are you familiar with the Filters area, which is in addition to Rows, Columns and Values? It’s very handy for focusing a view on a slice of your data. Eg I use it for looking at summary data for the current month—every month I just change the month in the Filters area.

      Lugh.
      ~
      Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
      i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

      1 user thanked author for this post.
    • #348904 Reply

      Kirsty
      Da Boss

      The current macros are:

          ActiveSheet.PivotTables("PivotTable1").PivotFields("Pay Date").ClearAllFilters
          ActiveSheet.PivotTables("PivotTable1").PivotFields("Pay Date").PivotFilters. _
              Add Type:=xlDateLastMonth
          Range("I31").Select
          ActiveSheet.PivotTables("PivotTable1").PivotFields("Pay Date"). _
              ClearLabelFilters

      Instead of “=x1DateLastMonth”, I need something like dateoflastentries or similar.

    • #349031 Reply

      Lugh
      AskWoody_MVP

      Instead of “=x1DateLastMonth”, I need something like dateoflastentries or similar.

      Beyond my pay grade 🙂 You need Retired Geek or Zeddy or Maudibe, they’ll sort you out in no time.

      Lugh.
      ~
      Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
      i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

      1 user thanked author for this post.
    • #349032 Reply

      debaser
      AskWoody_MVP

      You can find the highest date using Max assuming the data column is always in the same place (just takes a little more work if not), using something like:

      Dim LastDate as Date
      LastDate = Application.Max(Sheets("source_data_sheet").Range("A:A"))
      With ActiveSheet.PivotTables("PivotTable1").PivotFields("Pay Date")
          .ClearAllFilters
      
          .PivotFilters.Add Type:=xlSpecificDate, Value1:=LastDate
      
      End With
      1 user thanked author for this post.
      • #349155 Reply

        Kirsty
        Da Boss

        That sounds workable! TVM I’ll give it a try 🙂

      • #377090 Reply

        Kirsty
        Da Boss

        I was able to get the Max result to accurately give the last date, with a slight change to keep the pivot table happy:

        LastDate = Application.WorksheetFunction.Max(Sheet1.Range("A:A"))

        But I’m unable to get the x1SepcificDate value to fill. The debugger says on hover that “x1SpecificDate=empty”, but “value1:=LastDate” on hover shows LastDate= as the correct last date, properly formatted.

        As a result, running it gives a “run-time error ‘5’: invalid procedure call or argument”

        Any suggestion on how to get the x1SpecificDate pivotfilter to understand the value?
        Thanks.

    • #390170 Reply

      debaser
      AskWoody_MVP

      It’s xL not x1 at the start

      1 user thanked author for this post.
      • #402093 Reply

        Kirsty
        Da Boss

        Thanks for help spotting the obvious typo I was staring at and couldn’t see – two pairs of eyes are always better than one! 🙂

        However, the end result isn’t a happy one. Now, hovering over “xlSpecificDate,” it shows “xlSpecificDate=29”, and hovering over “Value1:=LastDate” is no longer showing the correct information – it now says “LastDate=12.00.00am”. Using a Msgbox to return the result of LastDate shows the correct date, correctly formatted. I even added new entries to the source data, and it picked that up just fine.

        By trying an actual date as the Value1:=, I was able to convince the debugger hover that “LastDate=” was the actual date of last entry, but it still isn’t filtering based on that, when clicking Run>Continue. xlSpecificDate still = 29 (maybe that is normal?)

        Some connection still isn’t gelling… 🙁

    • #406975 Reply

      Kirsty
      Da Boss

      I found a result using this line:

      Add Type:=xlSpecificDate, Value1:=CLng(LastDate)

      So the sub now looks like:

      Dim LastDate As Date
      LastDate = Application.WorksheetFunction.Max(Sheet1.Range("A:A"))
      
          With ActiveSheet.PivotTables("PivotTable1").PivotFields("Pay Date").ClearAllFilters
              ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
              ActiveSheet.PivotTables("PivotTable1").PivotFields("Pay Date").PivotFilters. _
                  Add Type:=xlSpecificDate, Value1:=CLng(LastDate)
                 
              End With
              
          End Sub

      Thanks very much for the help!

    • #413328 Reply

      debaser
      AskWoody_MVP

      Glad you got it working! 🙂

      1 user thanked author for this post.

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: VBA Macro Pivot Table Query

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.