VBA filter of large dataset.

Posted on 2015-01-29
Last Modified: 2015-01-30
I have a large workbook with basically one worksheet. The detail data is in Columns "AN" thru "ON"
Daily the reporting needs to filter the data to Previous week, Current Week, Upcoming Week by use of a single cell as the Data Date.
The reporting section is in Columns "J" thru "AD"
Changing Cell "C2" should change the dates in "J19 thru AD19" so that if "C2" is 16-Jan-15, then 16-Jan-15 would be in the Current Week section in Cell "W19" as the end of the Current week.
The corresponding data in Rows "20" thru"28" matching the dates in Row 19 should be copied from the detail section starting in Column "AN"  The values can just be Paste Value in Columns "J" thru "AD" from the detail section.

This same process needs to be repeated in all sections from Row 19 thru to 460 in blocks of 10 rows.

Since the data rows may grow or shrink over time, dynamic named ranges for columns would work best.

The columns are static, and will not change.

Help here would be so much appreciated.
Question by:DougDodge
  • 4
  • 3
LVL 29

Expert Comment

ID: 40579404
Didn't we already worked on something similar to this ? It had current and past week I see here it has upcoming week is this the only difference just a first glance.

Author Comment

ID: 40579470
Basically that was the only difference......
LVL 29

Expert Comment

ID: 40579506
Nope that was not !!! :)

You also had 3 rows per block now you have 4 times 2 with a blank separator !!! :)

Will revert shortly.
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

LVL 29

Accepted Solution

gowflow earned 500 total points
ID: 40579517
Here it is.

I recall had commented out the selection change event as was annoying to see the screen blinking all the time. You have commented out the exit sub is there a reason for that ?


Author Comment

ID: 40579664
Not that I can remember....
LVL 29

Expert Comment

ID: 40580058
Anyway try the file I did not change this selection change event but got you what you wanted in this question

Author Closing Comment

ID: 40580127
Works well...... Thank you once again.

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now