I've got 3 sheets with date columns like below:

ColumnE ColumnJ
-------- --------
3.31.04 10.1.03
11.26.03
11.30.04 7.28.03
12.09.03
12.31.03
1.30.03
3.30.05 7.11.03
12.31.03
7.29.03
3.31.03
8.31.04 1.04.03
6.20.03
9.30.05 12.25.02
12.31.04
11.30.05
9.13.03
10.10.03

9.19.05 7.22.03
6.19.03
2.28.04 10.6.03
12.31.03 10.15.03
8.31.04 12.15.03

3.31.04 8.26.03
10.31.04 7.30.03
10.31.04 4.1.03

Now, I need to iterate through the columns and
check to see if the date in the cell is <30 days
from today. Then I want to copy the info in Columns
A, E and J for that particluar row to a new sheet
created. This needs to be done for each of 3 sheets
in the workbook.

How would I do that?

MC

Here's an untested example of how you might do that.

Sub Untested()

Dim sh As Worksheet
Dim cell As Range
Dim rng As Range
Dim NwSht As Worksheet

Set NwSht = ThisWorkbook.Worksheets.Add _
(, ThisWorkbook.Worksheets(ThisWorkbook.Sheets.Count) )
NwSht.Name = "NewSheet"

For Each sh In ThisWorkbook.Worksheets
If sh.Name < "NewSheet" Then
Set rng = sh.Range("E1", sh.Range("E65536").End(xlUp))
For Each cell In rng.Cells
If cell.Value + 30 Date Or _
cell.Offset(0, 5).Value + 30 Date Then

With NwSht.Range("A65536").End(xlUp).Offset(1, 0)
.Value = cell.Offset(0, -4).Value
.Offset(0, 1).Value = cell.Value
.Offset(0, 2).Value = cell.Offset(0, 5).Value
End With
End If
Next cell
End If
Next sh


End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Mr. Clean" wrote in message
om...
I've got 3 sheets with date columns like below:

ColumnE ColumnJ
-------- --------
3.31.04 10.1.03
11.26.03
11.30.04 7.28.03
12.09.03
12.31.03
1.30.03
3.30.05 7.11.03
12.31.03
7.29.03
3.31.03
8.31.04 1.04.03
6.20.03
9.30.05 12.25.02
12.31.04
11.30.05
9.13.03
10.10.03

9.19.05 7.22.03
6.19.03
2.28.04 10.6.03
12.31.03 10.15.03
8.31.04 12.15.03

3.31.04 8.26.03
10.31.04 7.30.03
10.31.04 4.1.03

Now, I need to iterate through the columns and
check to see if the date in the cell is <30 days
from today. Then I want to copy the info in Columns
A, E and J for that particluar row to a new sheet
created. This needs to be done for each of 3 sheets
in the workbook.

How would I do that?

In article ,
says...
MC

Here's an untested example of how you might do that.


Thanks for the feedback. I was beginning to wonder if anyone
was going to help out. The things I tried failed miserably...

Mr.C

In article ,
says...
In article ,
says...
MC

Here's an untested example of how you might do that.


Thanks for the feedback. I was beginning to wonder if anyone
was going to help out. The things I tried failed miserably...

Mr.C


Is there a way to make a toolbar button execute this query?

In article ,
says...
In article ,
says...
In article ,
says...
MC

Here's an untested example of how you might do that.


Thanks for the feedback. I was beginning to wonder if anyone
was going to help out. The things I tried failed miserably...

Mr.C


Is there a way to make a toolbar button execute this query?


Nevermind, I found out how to do that.
Thanks for the help

Mr.C