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

phroureo

New Member
Joined
Nov 24, 2015
Messages
36
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Nov 24, 2015
Messages
36
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

New Member
Joined
Nov 24, 2015
Messages
36
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

New Member
Joined
Nov 24, 2015
Messages
36
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?
 

Forum statistics

Threads
1,141,403
Messages
5,706,252
Members
421,434
Latest member
DaltonB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top