?
Solved

VBA filter of large dataset.

Posted on 2015-01-29
7
Medium Priority
?
147 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 31

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 31

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 31

Accepted Solution

by:
gowflow earned 2000 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 31

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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Gain an elementary understanding of Blockchain technology.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

771 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