Need sopme help coding Dates and Sheet names. (Please)
I could use some help codeing please...
There have 5 worksheets, each a week of the month. From the Year and month inputed, it should name each sheet by the last day in each week. (Saturday) All the lines with a ' starting it was code I was not sure how to write. (some code ommited to clean things up) The following data is selected on the first Sheet. D2 = Month(TEXT) H2= Year(Number) If Not IsEmpty(D2) And Not IsEmpty(H2) Then 'SET SHEETDATE Month(D2) Year (H2) DAy(First Saturday of the Month) For Each Sh In ThisWorkbook.Worksheets If Sh.Index < 6 Then Worksheets(Sh.Index).Activate SheetDate = DateAdd("ww", (Sh.Index - 1), SheetDate) 'If this date is Not Past the end of the month in D2 Then Sh.Name = Format(SheetDate, "mmm-dd") 'Else Sh.Name = "Week " & Choose(Sh.Index, " 1", " 2", " 3", " 4", " 5") 'End if End If Next End If For example if D2 = Feburary and H2 = 2005 Then the sheets would name them selfs like so : Sheet 1 Renamed to Feb-05 Sheet 2 Renamed to Feb-12 Sheet 3 Renamed to Feb-19 Sheet 4 Renamed to Feb-26 Sheet 5 Renamed to Week 5 |
Need sopme help coding Dates and Sheet names. (Please)
Hello Dean, Here is a macro you can use. Add a module to your VBA project and paste this code in. You can select and run it from Excels menu "Tools | Macro | Macros... " The macro name is "RenameSheets". __________________________________________________ _______________ Public Function FindSaturdays(strMonth As String, intYear As Integer) As Variant Dim N As Integer Dim DaysInMonth As Integer Dim StartDate As Date Dim Saturdays(5) StartDate = "01-" & strMonth & "-" & LTrim(Str(intYear)) Select Case Month(StartDate) Case 2 DaysInMonth = 28 Case 4, 6, 9, 11 DaysInMonth = 30 Case Else DaysInMonth = 31 End Select For I = 1 To DaysInMonth If WeekDay(StartDate) = 7 Then N = N + 1 Saturdays(N) = Format(StartDate, "mmm-dd") End If StartDate = StartDate + 1 Next I FindSaturdays = Saturdays End Function Public Sub RenameSheets() Dim Saturdays Saturdays = FindSaturdays(Range("D2").Value, Range("H2").Value) For I = 1 To UBound(Saturdays) Sheets(I).Name = Saturdays(I) Next I End Sub_______________________________________________ __________________ Hope this helps, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=345795 |
All times are GMT +1. The time now is 06:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com