Increase Next Worksheet by 1 Month

Papi

Well-known Member
Joined
May 22, 2007
Messages
1,592
I use files that will typically have one month for each month of the year (12 in each workbook. The tabs are typically named Jan, Feb, Mar, Apr, May etc. A formula that I would normally use in cell A1 of the next worksheets (Not counting Jan) would be =EOMONTH(Jan!A1,0)+1. I then have to return to each worksheet and change the Feb to Mar and so forth.

What might a person be able to use to create the next worksheet which will do two things;

1) To change cell A1 to match the worksheet to the left and start with the first day of the next month i.e. if the last worksheet ended as Jan 31 then the new worksheet would be Feb 1

2) To change the file tab name to match the current month i.e. The above sample file tab would be Feb

I have a certain bit of code that works fine to do almost the same thing but the only difference is that it adds seven days and what I need is for it to make the next file tab and cell show the previous month plus one (1).

Sub CopySheet()
Application.ScreenUpdating = False
Sheets("Blank").Copy after:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
.Range("D1").Value = .Previous.Range("A1").Value + 7
.Name = Format(Range("D1").Value, "mmm")
Application.Goto Reference:="R7C2"
Application.ScreenUpdating = True
End With
End Sub

I would like to be able to have the first worksheet (Jan) and then create the next eleven worksheets to match evrything with the start of the next month. Other cells feed from cell A1 so that cell has to be the first day of the next month from the previous worksheet to the immediate left.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Could you just use this formula in cell A1 of every sheet?

=DATEVALUE("1-"&REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),""))

Provided the sheet name in a month name (or suitable abbreviation like "Feb") then that should return the date being the first of that month. You may need to format the cell appropriately after entering the formula.
 
Upvote 0
Hi Papi,

I think I have the code, if I fully understood your request. The best place to put this code is in a PERSONAL.XLSB Project module. If you don't have one in your VBE on the left, just search on 'Create PERSONAL.XLSB'.

Run the code from the PERSONAL.XLSB module. The code will create a new workbook with 12 worksheets. It will rename the default worksheet names with your 3-letter month. At the same time, it will place your formula in cell A1 on each sheet beginning with the Feb worksheet. It also changes each formula to refer to the previous month. I hope I understood that correctly.

Give this a try:
Code:
Public Sub CreateAnnualWorkbook()
Dim wsCount As Integer
MonthArray = Array(, "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
wsCount = 12        'Number of sheets in new workbook
 
Application.ScreenUpdating = False ' speed up code by turning off screen updating
On Error GoTo Release
 
Call NewWorkbook(wsCount)
 
For i = 1 To 12
    ' Create Month's Worksheets
    Sheets(i).Name = MonthArray(i)
    If i <> 1 Then
        Sheets(MonthArray(i)).Select
        Range("A1").Formula = "=EOMONTH(" & MonthArray(i - 1) & "!A1,0)+1"
    End If
Next i
Sheets("Jan").Select
Release:
    Application.ScreenUpdating = True ' turning on screen updating
End Sub
Function NewWorkbook(wsCount As Integer) As Workbook
' creates a new workbook with wsCount (1 to 255) worksheets
Dim OriginalWorksheetCount As Long
    Set NewWorkbook = Nothing
    If wsCount < 1 Or wsCount > 255 Then Exit Function
    OriginalWorksheetCount = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = wsCount
    Set NewWorkbook = Workbooks.Add
    Application.SheetsInNewWorkbook = OriginalWorksheetCount
End Function

I also need to give credit for the function to: http://www.exceltip.com/st/Create_new_workbooks_using_VBA_in_Microsoft_Excel/470.html

-dougbert
 
Last edited:
Upvote 0
Hello DougBert,

I have to say that your name is cool. I was using a Dell Computer (against my will) when the relatively new IT fellow approached me and said he needed a name for my log in...Yup, DellBert it was... :o)

You are on the correct track and I must add that it works like a bullet. Unfortunately, what I will typically have is a worksheet that is formatted with many formulas working at the same time i.e. a timesheet, monthly reports etc.

Usually, cell A1 will have the first date of a month i.e. the "Jan" worksheet would have January 1, 2011. What I am attempting to do is be able to run the first worksheet and create a second one with the next month and so forth to the end of the year.

The code I metioned earlier works to do something similar but only creates a new worksheet by looking at the previous worksheet and adds seven days to the next one. What I am looking for is to modify that code (below) to do the same thing by looking at cell A1 and creating a new worksheet with the first day of the next month and at the same time to create the file tab name as noted below. I believe that the only code that needs changing is ".Range("D1").Value = .Previous.Range("A1").Value + 7" to move to the next month instead of only seven days. i.e =EOMONTH(Previous Worksheet to the left and pointing at Cell A1,0)+1

Sub CopySheet()
Application.ScreenUpdating = False
Sheets("Blank").Copy after:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
.Range("D1").Value = .Previous.Range("A1").Value + 7
.Name = Format(Range("D1").Value, "mmm")
Application.Goto Reference:="R7C2"
Application.ScreenUpdating = True
End With
End Sub
 
Upvote 0
Hi Papi,

Try this:

Code:
Sub CopySheet()
Application.ScreenUpdating = False
Sheets("Blank").Copy after:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
    .Range("D1").Value = DateAdd("m", 1, .Previous.Range("A1").Value)
    .Name = Format(Range("D1").Value, "mmm")
End With
Application.Goto Reference:="R7C2"
Application.ScreenUpdating = True
End Sub
-dougbert
 
Last edited:
Upvote 0
Thanks to both gentlemen. This is great and saves a lot of time with increased efficiecy.

One question, as you note I use the blank worksheet to copy from. How difficult would it be to copy the last worksheet i.e. Aug and make a Sep worksheet rather than using the blank?
 
Upvote 0
One question, as you note I use the blank worksheet to copy from. How difficult would it be to copy the last worksheet i.e. Aug and make a Sep worksheet rather than using the blank?
The copy last sheet and A1 value and sheet name would be easy. Depends if you then have other date to remove from the sheet?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> NextMonthSheet()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count)<br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>        <SPAN style="color:#00007F">With</SPAN> .Range("A1")<br>            .Value = DateAdd("m", 1, .Value)<br>            .Parent.Name = Format(.Value, "mmm")<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>



Or if you have the 'Jan' sheet and want to create the other 11 for the year, just wrap that in a For ... Next loop


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> NextMonthSheet()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 11<br>        Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count)<br>        <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>            <SPAN style="color:#00007F">With</SPAN> .Range("A1")<br>                .Value = DateAdd("m", 1, .Value)<br>                .Parent.Name = Format(.Value, "mmm")<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Last edited:
Upvote 0
The first one works great Peter. The one thing that puts me in a bind is a request was added to include Year to Date (YTD) figures of which there are two ranges in each worksheet that look back at the previous worksheet. The formulas are i.e. for the July worksheet it would be =Jun!AI3+AH3 (In ranges AI3:AI20 and AI24:AI41). As well, I tweaked your code just slightly to "mmm yy" to ensure that the worksheets do not have to same file names if and when the worksheets cross from one year to the next. Does this make sense?
 
Upvote 0
The first one works great Peter. The one thing that puts me in a bind is a request was added to include Year to Date (YTD) figures of which there are two ranges in each worksheet that look back at the previous worksheet. The formulas are i.e. for the July worksheet it would be =Jun!AI3+AH3 (In ranges AI3:AI20 and AI24:AI41). As well, I tweaked your code just slightly to "mmm yy" to ensure that the worksheets do not have to same file names if and when the worksheets cross from one year to the next. Does this make sense?
Try this one.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> NextMonthSheet()<br>    <SPAN style="color:#00007F">Dim</SPAN> OldM <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> f <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "='#'!AI3+AH3"<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count)<br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>        <SPAN style="color:#00007F">With</SPAN> .Range("A1")<br>            OldM = Format(.Value, "mmm yy")<br>            .Value = DateAdd("m", 1, .Value)<br>            .Parent.Name = Format(.Value, "mmm yy")<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        .Range("AI3:AI20,AI24:AI41").Formula = Replace(f, "#", OldM)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0

Forum statistics

Threads
1,224,599
Messages
6,179,831
Members
452,946
Latest member
JoseDavid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top