Solved

VBA filter of large dataset.

Posted on 2015-01-29
7
139 Views
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.
SITE-PROGRESS.xlsm
0
Comment
Question by:DougDodge
  • 4
  • 3
7 Comments
 
LVL 29

Expert Comment

by:gowflow
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.
gowflow
0
 

Author Comment

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

Expert Comment

by:gowflow
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.
gowflow
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 29

Accepted Solution

by:
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 ?

gowflow
SITE-PROGRESS-V02.xlsm
0
 

Author Comment

by:DougDodge
ID: 40579664
Not that I can remember....
0
 
LVL 29

Expert Comment

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

Author Closing Comment

by:DougDodge
ID: 40580127
Works well...... Thank you once again.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…

803 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