Is it possible to define fields to be selected in a Pivot Table based on the date using VBA?

[phroureo]

More information: I am creating a P&L report based on data imported from SQL Server. On each tab, I have a different portion of the data.

In the columns, it's grouped by month from the TaxDate column, with Sum of Total in the values field.

I'm hoping to create a button that will copy the data from each of the pivot tables and paste it into another sheet, while selecting the appropriate columns to show.

The issue is that I want to select the columns from the previous 10 months. I tried using a Macro (my VBA is mediocre at best) to select the correct columns. This will be useful for approximately seven more days, until December starts. I need to have it so that the dates that are selected will be the correct ones, including after the current month.

Is this making sense? I sure hope so, because I don't know if I really am.

I feel like I probably have to declare a variable based on the current month, and then use that to select the appropriate date range, but I'm clueless as to what the appropriate syntax would be (I do SQL, not VBA :P)

Thanks for the help.

 

[Smitty]

Welcome to the Board!

What version of Excel are you using? If it's 2013+ I'd use a Timeline Slicer, which will allow you to select the time period you want to display. Although if your data is grouped by Month/Columns it sounds like it's already in a pivoted format, so you'd probably want to go to the raw data.

If you follow the HTML Maker link in my sig you'll be able to post a screen shot of what you've got now and what you're trying to achieve.

 

[phroureo]

Oops, yeah, I guess I should have been more clear.

The table that I'm working from is just straight journal entries, and I'm using Pivot Tables to summarize the data into a Profit And Loss format. However, I have ~18 different Pivot Tables (6 different revenue, 6 different COGS, and 6 different Expense).

I'm going to be using VBA to copy them all into a single sheet, but I want to 1) show all columns, even if they are empty, and 2) only show data from the past 11 months.

While I could manually set the data to show the past 11 months, this is eventually going to go out to a client, and I'm 100% certain that they don't want to


_______________________________


And this is the point where I solved my own problem, and realized that I can just cause the data that I want to extract to be pulled out in the SQL query.

Granted, I'm sure I'll come up with more dumb issues (because I haven't done any VBA for about two years (when I was in college) and even then it was very baby-sat, in a "do this, then this, then this" kind of way that didn't foster actual problem solving.

 

[phroureo]

Alright, new, semi-related question:

In a Pivot table, I'm trying to get it to filter by month, but I don't want to use data from other years. For example, if I have a data set from April 2014 to September 2015, I don't want to have the data from August 2014 added to the August 2015 data--I'd prefer to have them in separate columns, even if they're both called August.

 

[phroureo]

1) Is there not an edit button?

2) To expand on the last few questions that I've managed to half-articulate before giving up, say that I pull data from November 2014 through October 2015. I want the columns to show up in chronological order (that is, Nov, Dec, Jan, Feb... etc.) instead of starting at January and putting the other info on the end. Is there a way to do that?