I want to write code of the week

HESHAM63

Board Regular
Joined
May 3, 2010
Messages
75
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,770
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

Board Regular
Joined
May 3, 2010
Messages
75
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,770
If there is an actual serial date in Sheet1 A1 (formatted "dddd" to show Monday") a loop like this should work

Code:
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"


Code:
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
 

Fowmy

Well-known Member
Joined
Jul 10, 2008
Messages
512
Office Version
365
Platform
Windows
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,452
Messages
5,511,468
Members
408,850
Latest member
NewUserPri

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top