Hi All,

I would really appreciate some help with the following:

The first sheet in my workbook is called "Control".
The second sheet is called "A"
The last sheet is called "B"
In between sheets "A" and "B" are additional worksheets, one for
everyday of a month for example May 1 through to May 31.
On the "Control" worksheet I have starting in cell A2 the date
May 1 followed by May 2 etc.

What I need is some VBA code that will allow me to rename
all the worksheets that are in-between the "A" and "B" worksheets
with the list of dates that appear on the "Control" worksheet.

In essence as the user enters a new date for example June 1 in
A2 on the Control worksheet and drags the date down the column until it
reaches June 30,
all the sheets between "A" and "B" need to be updated.

Any ideas or suggestions on how to accomplish the above?

Thanks,

Steve

Steve,

You don't have to drag the names down, all you need is a start date in A2

Sub RenameSheets()
Dim i As Long
Dim sh As Worksheet
Dim dte As Date
Dim dte1 As Date

If IsDate(Worksheets("Control").Range("A2").Value) Then
dte1 = CDate(Worksheets("Control").Range("A2").Value)
dte = dte1
For Each sh In ActiveWorkbook.Worksheets
If sh.Name < "Control" And sh.Name < "A" And _
sh.Name < "B" Then
If Month(dte) = Month(dte1) Then
sh.Name = Format(dte, "mmm d")
sh.Visible = xlSheetVisible
dte = dte + 1
Else
sh.Visible = xlSheetHidden
End If
End If
Next sh
Else
MsgBox "Start position invalid"
End If
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steve" wrote in message
news:%5jaf.389372$oW2.362335@pd7tw1no...
Hi All,

I would really appreciate some help with the following:

The first sheet in my workbook is called "Control".
The second sheet is called "A"
The last sheet is called "B"
In between sheets "A" and "B" are additional worksheets, one for
everyday of a month for example May 1 through to May 31.
On the "Control" worksheet I have starting in cell A2 the date
May 1 followed by May 2 etc.

What I need is some VBA code that will allow me to rename
all the worksheets that are in-between the "A" and "B" worksheets
with the list of dates that appear on the "Control" worksheet.

In essence as the user enters a new date for example June 1 in
A2 on the Control worksheet and drags the date down the column until it
reaches June 30,
all the sheets between "A" and "B" need to be updated.

Any ideas or suggestions on how to accomplish the above?

Thanks,

Steve

Wow Bob, the code works great!
Thanks again for your help.

Cheers,

Steve



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