All the help has been great thanks, but now I have a new kind of problem.
I have an excel sheet which has a column A with dates, and toher column of data.
I need to be able to copy the data for the week I am in depending on which Day I select and also copy the following week, the last code I was workign on would give me 14 days ahead from a given start date.
What i need to create is a smart macro which recognizes which day of the week it is then goes back a few days to include that intial entire week and then moves forward to include the following week.
So lets say I start by clicking on a date which is a Thursday, i need the macro to select all the days behind thursday (monday, tues, weds & STOP AT SATURAY), then i need it to look forward to teh next week and select all days for the following week Monday Through Friday.
If I select 7/8/05 which is a Friday, I want the macro to go back a few dates all the way to begenning of week at Tuesday 7/05/05. Then I want it to also look forward and copy the days in the following week which are tues 7/21 through Fri 7/15
I know this sounds complicated, thats why I cam to the experts, any thoughts guys?