VBA advice on how to handle quarterly sales filtering only.

[sintek]

Hi All

Am sitting with a problem and need some input of how to handle quarterly sales filtering...
Scenario is as follows:
Every month my report must show only the last 3 months with last month compared to last month of previous year. i.e
Report Current
Mar2017 Apr2017 May2017 May2016
Next Month Report
April2017 May2017 June2017 June2016
Following Month Report
May2017 June2017 July2017 July2016
etc
Have a spreadsheet with Headings Jan - Dec
Would I tackle this with code to hide columns based on Current month and code to populate previous year month with current month search in last years figures?

Have no clue where to start...Any input would be valuable.

[CK76]

Hmm, normally I'd leverage data model along with PivotTable for this type of thing. But since you have 2007, data relationship isn't available.

I'd probably start from tranforming/merging data into single flat table for both year (or for each year) and then summarize using formula or pivot table.

Using VBA to dynamically filter pivottable.

[sintek]

Hi CK76

Thanx for input...My pet hate is Pivot Tables...lol...Perhaps cause I have had little or no experience with them.

[kev_]

How about this

Select value in cell A1 for your 4 columns
(Double click in cell A1 to show all columns)

Please Login or Register  to view this content.

[sintek]

Hi all

Have come up with code to handle the filter:

Please Login or Register  to view this content.

[sintek]

Hi kev_

Awesome solution...Quick question:
If the previous years figures were on a separate sheet called 2016 Columns B through M...
How would you adapt code?

[kev_]

If you want to use different sheets for the data in different years then hiding & unhiding will not work
Select value in dropdown in A1 in sheet "Report"

Please Login or Register  to view this content.

[sintek]

Nice one kev_

Thanks...Will most certainly make use of.

[kev_]

or .... create a lookup table to tell VBA where to find the 4 columns - which simplifies the code

In attached workbook
- Table2 contains a list of the 24 months and where to find them
- Table1 looks up Table2
- VBA uses Table1 to paste the 4 columns

Please Login or Register  to view this content.