I want to write code of the week

[HESHAM63]

Hello guys
Suppose we have a file excel Containing 31 sheet that the number of days of the month
I want to write today's date in the cell A1 IN sheet1
For example
in the cell A1 IN sheet1 Write Sunday
I want write automatically by code in the cell A6 IN sheet2 Write Monday
and n the cell A6 IN sheet3 Write Tuesday
and n the cell A6 IN sheet4 Write Wednesday
And so on until the sheet No. 31 last day of the month

 

[mikerickson]

Today is not the first of the month.
Do you still want today's date in Sheet1!A1?

If so, do you want this to continue until a date in next month is written in Sheet31 or should the routine stop immediately after the last day in this month is written on some sheet?
If the second scenario, should A6 of the remaining sheets be cleared or their contents left undisturbed?

 

[HESHAM63]

Thank you for your reply
in sheet main the date set manually by user
say (Monday)
i want by code or any way
in sheet1 Writes Thursday
in sheet2 Writes Friday ........ And so on
And finish sheet 31

 

[mikerickson]

If there is an actual serial date in Sheet1 A1 (formatted "dddd" to show Monday") a loop like this should work

Dim i As Long

For i = 2 to 31
    Worksheets("Sheet" & i).Range("A1").Value = Worksheets("Sheet" & (i-1)).Range("A1").Value + 1
Next i

If Sheet1A1 holds the text string "Monday"

Dim i As Long
Dim arrayOfDays As Variant
Dim currDay As String
arrayOfDays = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday", "Monday")

currDay = CStr(Worksheets("Sheet1").Range("A1").Value)

If IsError(Application.Match(currDay, arrayOfDays, 0) Then
    MsgBox "Enter day in Sheet1 in proper format"
Else
    For i = 2 to 31
        currDay = arrayOfDays(Application.Match(currDay, arrayOfDays, 0))
        Worksheets("Sheet" & i).Range("A1").Value = currDay
    Next i
End If

 

[HESHAM63]

please see that
http://www.herosh.com/download/5595561/hh.rar.html

 

[Fowmy]

Hi Hicham,

I tried a formula solution,

Select all sheets from 1 to 31 and select cell A6 and enter the following formula

=main!$A$1+(--MID(CELL("filename",A1),SEARCH("]",CELL("filename",A1))+1,2))

Press CTRL + ENTER