Forum Discussion
Solved
Automatically name a new worksheet with a date sequence
Hello,
I am trying to create a workbook in which new worksheet names are automatically showing the current date and month.
For example, i have a worksheet named May 2019. I would like to insert a new worksheet that is automatically renamed to Jun 2019. When i create one in July, it would automatically be renamed to Jul 2019 and so on...
Is there a way to do this, please?
Thank you.
Does this work?
Sub Index_Match() Dim ws As Worksheet 'current sheet Dim exportWb As Workbook Dim clRequestWb As Workbook Dim v As Variant Set clRequestWb = ThisWorkbook Set ws = clRequestWb.Worksheets("Sheet1") Set exportWb = Workbooks.Open("C:\Users\hrhquek\desktop\export.xlsx") v = Application.Match(ws.Cells(15, 3), exportWb.Sheets("Sheet1").Range("E:E"), 0) If IsError(v) Then v = Application.Match(ws.Cells(15, 3), clRequestWb.Sheets("No SAP ID").Range("B:B"), 0) If IsError(v) Then MsgBox "Credit Limit is not allocated to this customer" Else ws.Cells(14, 3).Value = Application.Index(clRequestWb.Sheets("No SAP ID").Range("A:A"), v) End If Else ws.Cells(14, 3).Value = Application.Index(exportWb.Sheets("Sheet1").Range("B:B"), v) End If End Sub
11 Replies
- Honestly, having a worksheet for each month will not make your life simpler as soon as you're asked to do reporting on your file. If you want to show an overview of monthly totals, a single table which includes a date column is a lot simpler.
JKPieterse I am not looking for any reporting afterwards. We are looking at each sheet in the month it occurs. There will be too much data on one sheet to put all the months on one sheet. But thank you for your suggestion.
AnaPistol This macro checks if there alreay is a worksheet for the current month. If not it creates one. If there is, it selects it:
Sub AddMonthSheet() Dim sName As String Dim oSh As Worksheet sName = Format(Date, "yyyy-mmm") On Error Resume Next Set oSh = Worksheets(sName) If oSh Is Nothing Then Set oSh = Worksheets.Add End If oSh.Name = sName oSh.Activate End Sub
