Create a new and renamed worksheet but still keep the orginal content

[VBA NQG]

Hi all,

I have a 2010 excel sheet and its layout is as below:

(Column) A B C D E F
No Supervisor Type 01/01/2014 02/01/2014 03/01/2014

The sheet contains 70 rows and column B, D, E, F are drop down boxes.

I need a VBA to generate a new work sheet which contains the original content and drop down boxes. However in the new sheet the date will be the start of next week, such as 08/01/2014, 09/01/2014...

For the newly created active work sheet, it needs to have new name to reflect the first date of the sheet, such as 01/01/2014 and next sheet is 08/01/2014.

Can someone help me with this? Thank you!

[nathansav]

range("what ever cell").value =date-(weekday(date())-2)

thisworkbook.saveas "MYBOOK" & format(date-(weekday(date())-2),"ddmmyyyy") & ".xlsx"

[Coleman34]

My apologies, I read this wrong and posted a save as macro, Nathansav has it succinctly.

[VBA NQG]

Thank you all !