Chris,

Just a clarification:
For example: cell AQ5 contains the date: 01-Dec-09. The date
five-months prior to this would be: 01-Nov-09.

Five months prior to 01 Dec 09 is 01 July 09. But 5 months from today is 28 Oct 09 - which date do
you want to use as the basis of your comparison?

Anyway, once you decide, the way to approach this problem is not to use a macro but to have a column
of formulas:

=AND(AQ3 = DATE(YEAR(TODAY()),MONTH(TODAY())+5,DAY(TODAY())), X3="VACANT")

Copied down to match your data set, then apply a data filter on that column, choose TRUE and then
copy only the rows that are visible after the filtering.

HTH,
Bernie
MS Excel MVP


"Chris" wrote in message ...
Hello, could someone please help me with the following? I need a macro
that looks up column AQ (a date column).

I then need the macro to calculate the date five-months prior to the
dates in range AQ3 : AQ65000.

With the new calculated date (five-months prior to date) - I need to
determine if the associated values in column X contain the text
"VACANT".

So I need the macro to lookup column X (a text column) and if any of the
cells in range X3 to X65000 contain the text "VACANT" then that record
needs to be copied to another open workbook named 5 Months Out.xls, and
onto worksheet named: 5 Months Out. The copied rows need to be pasted
onto the new worksheet starting at cell: A3.

For example: cell AQ5 contains the date: 01-Dec-09. The date
five-months prior to this would be: 01-Nov-09. In Column X there are
cells in range X3:X65000 that contain the text "VACANT". These are
located in cells: X5 and X24. Thus, the entire rows (row 5 and row 24)
need to be copied to the open workbook named 5 Months out.xls onto
worksheet named: 5 Months Out into cell A3 (all of row 3).

I am using Excel 2003 with Win XP.

Any help in this macro would be greatly appreciated.

Kind regards,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***