Modifying sheet names for Months instead of days

[Enzo_Matrix]

I am using this code:

Sub NewMonth()' Making a new sheet with the month name changing


Sheets(Format(Date, "mmm")).Copy Before:=Sheets(Sheets.Count)
   ActiveSheet.Name = Format(Date + 1, "mmm")
   Range("A2").Select
End Sub

From my understanding it should change the copied sheet name from 'Mar' to 'Apr'. Instead it changes it from 'Mar' to 'Mar (2)'. Can someone please help me refine my code so it changes the sheets name to the following month.

 

[sheetspread]

Try Date+31

 

[Enzo_Matrix]

Will this also work with months that have 30 days or 28 days?

 

[sheetspread]

If the dates are all the first of the month or close to it, if month end then use a smaller number.

 

[Dave Patton]

The 31 should be OK.

Excel 2010
	A	B	C	D
38	01-01-16	31-01-16	Feb	Feb
39	01-02-16	29-02-16	Mar	Mar
40	01-03-16	31-03-16	Apr	Apr
41	01-04-16	30-04-16	May	May
42	01-12-16	31-12-16	Jan	Jan
43
3c

Cell Formulas
Range	Formula
C38	=TEXT(A38+31,"mmm")
D38	=TEXT(A38+31,"mmm")

 

[sheetspread]

The 31 should be OK.

Hopefully, this is what I was concerned about:

Excel 2010
	A	B	C
1	3/31/2018	5/1/2018	May
Sheet6

Cell Formulas
Range	Formula
B1	=A1+31
C1	=TEXT(B1,"mmm")

 

[Dave Patton]

I posted an edit to my post but it does not show.

Excel 2010
	A	B	C	D	E	F
38	1-Jan-16	31-Jan-16	Feb	Feb	1-Feb-16	1-Feb-16
39	1-Feb-16	29-Feb-16	Mar	Mar	3-Mar-16	3-Mar-16
40	1-Mar-16	31-Mar-16	Apr	Apr	1-Apr-16	1-Apr-16
41	1-Apr-16	30-Apr-16	May	May	2-May-16	2-May-16
42	1-Jan-18	31-Jan-18	Feb	Feb	1-Feb-18	1-Feb-18
43	1-Feb-18	28-Feb-18	Mar	Mar	4-Mar-18	4-Mar-18
44	1-Mar-18	31-Mar-18	Apr	Apr	1-Apr-18	1-Apr-18
45	1-Apr-18	30-Apr-18	May	May	2-May-18	2-May-18
46	1-May-18	31-May-18	Jun	Jun	1-Jun-18	1-Jun-18
47	1-Jun-18	30-Jun-18	Jul	Jul	2-Jul-18	2-Jul-18
48	1-Jul-18	31-Jul-18	Aug	Aug	1-Aug-18	1-Aug-18
49	1-Aug-18	31-Aug-18	Sep	Sep	1-Sep-18	1-Sep-18
50	1-Sep-18	30-Sep-18	Oct	Oct	2-Oct-18	2-Oct-18
51	1-Oct-18	31-Oct-18	Nov	Nov	1-Nov-18	1-Nov-18
52	1-Nov-18	30-Nov-18	Dec	Dec	2-Dec-18	2-Dec-18
53	1-Dec-18	31-Dec-18	Jan	Jan	1-Jan-19	1-Jan-19
54
3c

Cell Formulas
Range	Formula
C38	=TEXT(A38+32-DAY(A38),"mmm")
D38	=TEXT(B38+32-DAY(B38),"mmm")
E38	=A38+32-DAY(A38)
F38	=B38+32-DAY(B38)

01-01-18 Feb 1-Feb-18
31-03-18 Apr 1-Apr-18