Need help with a macro

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
I have a spreadsheet for each month of the year, all in the same workbook. The sheet tabs represent each day of the month. I name the tabs, Monday 9-01-02, so on until the end of the month. Cell "B1&C1"are merged and have the "Day" typed in. Cell "D1&E1" are merged and have the date typed in. When I type the date in "D1", it will date the rest of the sheets through the end of the month. I have tried to write a Macro to do this, and it would not work. Can someone help me with this?

Thanks,
DaveB
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

bolo

Active Member
Joined
Mar 23, 2002
Messages
423
alright this macro should do what you want.
It is set up for October but it can easily changed for any month. Let me know if it works for you.

Public Sub nameSheets()
Dim i As Integer
Dim week(7) As String
Dim month as string

week(1) = "Monday"
week(2) = "Tuesday"
week(3) = "Wednesday"
week(4) = "Thursday"
week(5) = "Friday"
week(6) = "Saturday"
week(7) = "Sunday"
month = "-10-02"
For i = 2 To 31
Worksheets.Add
ActiveSheet.Name = week(1 + i Mod 7) & " " & i & month
Next i
End Sub
 
L

Legacy 98055

Guest
Hi Dave.
This is similiar to Bolo's reply except the value in D1 is validated and sheets are renamed as is but added if nec.
Right-click on your worksheet tab and choose view code, paste in.
<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer, NextDay As Date
On Error Resume Next
If Target.Address = "$D$1" Then

If Not IsDate(Target.Value) Then
MsgBox "Invalid Date"
Target.Select
Exit Sub
End If

If MsgBox("Rename all of your worksheets for " & _
Format(Target, "MMMM YYYY") & "?", vbYesNo) = vbNo Then Exit Sub

NextDay = Target.Value

Do
i = i + 1
NameAdded:
Sheets(i).Name = Format(NextDay, "DDDD M-D-YY")
If Err.Number = 9 Then
Err.Clear
Sheets.Add , Sheets(i - 1)
GoTo NameAdded
End If
NextDay = NextDay + 1
Loop Until Month(NextDay) <> Month(Target.Value)

End If
End Sub

</pre>
Tom
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,972
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
To create sheets for a new month, open a new WB and name it.
Name a Sheet "the names" and add the names that you want to use for the sheets.

To create the names in YOUR format, you could put Year in C1 (2002), month number in C2 (10)
A1 is =DATE(C1,C2+1,0) A2 is =A1-1 Copy down 31
custom format dddd mm-dd-yy
Select the range A1:A31 name it "SheetNames",
Copy the range, then Edit Paste Special as Values

Add the following Sub

Sub NameS()

Dim I As Integer
For I = 1 To 31
Worksheets.Add
ActiveSheet.Name = Sheets("the names").Cells(I, 1).Value
Next
End Sub


The above is untested. Please advise if it works OK.

The above should work with a list of
unique valid names.
This message was edited by Dave Patton on 2002-10-06 22:02
 

Forum statistics

Threads
1,144,441
Messages
5,724,378
Members
422,547
Latest member
Vision1291

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top