Macros

I'm looking to create a macro that would create a new sheet pulling information off the most recently used sheet.  For example to keep track of sales and inventory each week.  So I want it to create a new sheet, name the sheet with the date of the last sheet plus 7 days, then pull the ending inventory from that week to the new sheet.  Basically creating a sheet for each week of the year automatically and working off the numbers from the most recent week.