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.
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.