albegood4u
New Member
- Joined
- Apr 29, 2023
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
i have had some help and self taught from various sources..
But i am stuck on what a simple question!
its almost near Perfect, Just cant seem to change the Name in the Tabs to the correct format!
Based on May 2023,
Tabs now renamed as (01 05) and so on
But i would like them to be 01 May and so on..
i was playing around and after many errors i managed the following...
(i know it will look simple to some what i done, but with No VBA or Macro experience)
Sub DuplicateTemplateSheet()
Dim i As Integer, m As Integer, y As Integer
Dim ws As Worksheet: Set ws = Sheets("MASTER COPY")
d = Day(ws.Range("E3"))
m = Month(ws.Range("E3"))
y = Year(ws.Range("E3"))
For i = 1 To Day(Application.EoMonth(ws.Range("E3"), 0))
Sheets("MASTER COPY").Copy after:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
.Range("E3") = Format(DateSerial(y, m, i), "dd mmm")
.Name = Format(i, "00") & " " & Format(m, "00")
End With
Next i
End Sub
i know i am missing something so simple!
But i am stuck on what a simple question!
its almost near Perfect, Just cant seem to change the Name in the Tabs to the correct format!
Based on May 2023,
Tabs now renamed as (01 05) and so on
But i would like them to be 01 May and so on..
i was playing around and after many errors i managed the following...
(i know it will look simple to some what i done, but with No VBA or Macro experience)
Sub DuplicateTemplateSheet()
Dim i As Integer, m As Integer, y As Integer
Dim ws As Worksheet: Set ws = Sheets("MASTER COPY")
d = Day(ws.Range("E3"))
m = Month(ws.Range("E3"))
y = Year(ws.Range("E3"))
For i = 1 To Day(Application.EoMonth(ws.Range("E3"), 0))
Sheets("MASTER COPY").Copy after:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
.Range("E3") = Format(DateSerial(y, m, i), "dd mmm")
.Name = Format(i, "00") & " " & Format(m, "00")
End With
Next i
End Sub
i know i am missing something so simple!