VBA Macro Pivot Table Query

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Kirsty wrote:

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.

woody

Reply | Quote

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.

Reply | Quote

Kirsty wrote:

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.

Kirsty

Reply | Quote

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.

Kirsty

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

It’s xL not x1 at the start

1 user thanked author for this post.

Kirsty

Reply | Quote

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…

Reply | Quote

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!

Reply | Quote

Glad you got it working!

1 user thanked author for this post.

Kirsty

Reply | Quote