Bringing only 'upcoming' dates to a new sheet?

[joshuakaplan1]

Hello,
This may be a relatively simple problem to solve or it might be
downright impossible. That's the fun thing about excel... you never
know!

I have a list of information on a worksheet containing 4 columns. In
column 3, a date is listed. I need to find a formula that will copy
ONLY those rows which contain a date occuring in the upcoming 6 weeks,
and bring those rows onto a new worksheet. If it would be possible to
have the rows stacked without any blank rows between them, that would
be ideal.

The new worksheet should contain all info for the upcoming 6 weeks
based on the formula. It will ideally be legible by non-excel-savvy
users on a single worksheet without having to be sorted or any extra
blank rows deleted.

Thanks in advance.

 

[Geoff Lilley]

There's not a formula per se I can think of that would do that, but I
could be wrong. That's just because I'm probably macro-happy.

But here's the beginnings of the formula. If column D has the date,
you could do something like this:
=IF(D2<=now()+42,"Yes","No")

If you want to ensure that the formula above is populated to EVERY row
of data, then you might want to use the List Manager. (That's not
something I say every day, but this sounds like a case where it might
be appropriate.)

Then, run the following code:

Sub sixWeeksOut()
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:="yes"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
End Sub

In short, the code does this:
1) AutoFilter the data, using Column 5, looking for the word "yes."
2) Copy ONLY the visible cells
3) Paste the cells into a new worksheet

HTH
Cheers
Geoff


What this function says, in essence, is, "Is the date in D2 less than
or equal to 42 days from today's date?"

 

[JE McGimpsey]

One way (using advanced filter)

Assume your data is laid out like:

A B C D
1 Heading1 Heading2 Heading3 Heading4
2 data data 2007-01-03 data
3 ...

1) Add this to your sheet (any column other than A):

J
1 <== leave blank
2 =AND(C2>=TODAY(),C2<=(TODAY()+42))

Note that J2 is a formula - it should return either TRUE or FALSE,
depending on the value in C2.

2) Select Sheet2, and choose Data/Filter/Advanced Filter. Click the
"Copy to another location" radio button. Enter

List Range: Sheet1!A
Criteria Range: Sheet1!J1:J2
Copy to: A1

and click OK

If you're going to do this regularly, you can do it via macro. This
macro creates a new sheet to the right of Sheet1, tries to name it "xxx
- yyyy" where xxx is today, and yyy is 6 weeks out from today, then
copies the relevant records to the new sheet. You can set rCriterion to
any one-column, two-row range that is guaranteed to be blank:

Public Sub Copy6WeeksOutToNewSheet()
Dim wsNew As Worksheet
Dim rCriterion As Range
Application.ScreenUpdating = False
With Worksheets("Sheet1")
Set wsNew = Worksheets.Add(After:=.Cells.Parent)
On Error Resume Next
wsNew.Name = Format(Date, "dd mmm yyyy \- ") & _
Format(Date + 42, "dd mmm yyyy")
On Error GoTo 0
Set rCriterion = .Range("J1").Resize(2, 1)
rCriterion(2).Formula = "=AND(C2>=TODAY(),C2<=(TODAY()+42))"
.Columns("A").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=rCriterion, _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
rCriterion.Clear
.Activate
End With
Application.ScreenUpdating = True
End Sub

One advantage of using the macro is that you don't need to have the
destination sheet be the active sheet.

 

[JE McGimpsey]

Of course, the formula

=ABS(C2-TODAY()-21)<=21

is far more efficient than

=AND(C2>=TODAY(),C2<=(TODAY()+42))