Naming worksheets within a macro

[Guest]

Hi

I want to create a macro that copies data from an Access table to an Excel
worksheet, inserting a new worksheet each week, copying the data on to it and
naming it (the worksheet) with the date the data was copied. So I would have
a series of worksheets named 1 Nov 06, 8 Nov 06, 15 Nov 06 etc. Is there a
way I can get the macro to name each new worksheet sequentially like this?

Many thanks

GLS

 

[Guest]

Here is a start:

Sub Macro1()
Sheets.Add
ActiveSheet.Name = Application.WorksheetFunction.Text(Now, "dd mmm yy")
End Sub

This will insert a new sheet in the workbook and assign it today's date as
the tab name.