Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 16th 05, 08:32 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2004
Posts: 18
Default 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


  #2   Report Post  
Old February 16th 05, 11:45 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2005
Posts: 1
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA coding for expiry dates alert threw email Tia[_3_] Excel Worksheet Functions 1 July 20th 09 03:12 PM
Color coding dates in excel Hilly New Users to Excel 7 April 2nd 09 12:05 PM
work sheet coding Dad Excel Discussion (Misc queries) 3 December 23rd 07 11:08 PM
Hidding Macro names and coding mrbalaje Excel Discussion (Misc queries) 4 April 20th 05 04:23 PM
Coding to check for a sheet name and add it if it's not there AlanN Excel Programming 2 August 21st 03 09:17 PM


All times are GMT +1. The time now is 08:00 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017