Excel Monthly Generation

[jennivy30]

I keep an Excel book for one of my customers to monitor hours, labor rates, and work performed. Each month I have to recreate the tabs to input the new data. Is there a formula or any way to regenerate the tabs monthly (minus the data that I input) without having to manually do it myself?

 

[dreid1011]

How many tabs are you creating? A very simple method would be to create a template of each tab that you could copy each month and rename. Another method building on this, you could use some vba to automatically copy the template tabs and rename them.

 

[shina67]

Sub CreateNewTabs()
Dim templateSheet As Worksheet
Dim newSheetName As String
Dim monthName As String
Dim year As String
Dim i As Integer
Dim numberOfTabs As Integer

' Set the template sheet
Set templateSheet = ThisWorkbook.Sheets("Template") ' Change "Template" to the name of your template sheet

' Get the current month and year
monthName = Format(Date, "MMMM")
year = Format(Date, "YYYY")

' Determine the number of tabs you want to create
numberOfTabs = 12 ' You can change this number to create tabs for different months

' Loop to create new tabs
For i = 1 To numberOfTabs
' Generate new sheet name
newSheetName = monthName & " " & year

' Check if the sheet already exists
If Not SheetExists(newSheetName) Then
' Copy the template sheet
templateSheet.Copy After:=Sheets(Sheets.Count)

' Rename the new sheet
ActiveSheet.Name = newSheetName

' Increment month for next iteration
If monthName = "December" Then
monthName = "January"
year = CStr(CInt(year) + 1)
Else
monthName = WorksheetFunction.Text(DateSerial(0, Month(Date) + 1, 0), "MMMM")
End If
End If
Next i
End Sub

Function SheetExists(sheetName As String) As Boolean
Dim ws As Worksheet
SheetExists = False
For Each ws In ThisWorkbook.Sheets
If ws.Name = sheetName Then
SheetExists = True
Exit
Function
End If
Next ws
End Function

This code will create new tabs for each month based on a template tab named "Template". You can adjust the number of tabs created by changing the value of numberOfTabs. Make sure to replace "Template" with the name of your template sheet.

 

[jennivy30]

Sub CreateNewTabs()
Dim templateSheet As Worksheet
Dim newSheetName As String
Dim monthName As String
Dim year As String
Dim i As Integer
Dim numberOfTabs As Integer

' Set the template sheet
Set templateSheet = ThisWorkbook.Sheets("Template") ' Change "Template" to the name of your template sheet

' Get the current month and year
monthName = Format(Date, "MMMM")
year = Format(Date, "YYYY")

' Determine the number of tabs you want to create
numberOfTabs = 12 ' You can change this number to create tabs for different months

' Loop to create new tabs
For i = 1 To numberOfTabs
' Generate new sheet name
newSheetName = monthName & " " & year

' Check if the sheet already exists
If Not SheetExists(newSheetName) Then
' Copy the template sheet
templateSheet.Copy After:=Sheets(Sheets.Count)

' Rename the new sheet
ActiveSheet.Name = newSheetName

' Increment month for next iteration
If monthName = "December" Then
monthName = "January"
year = CStr(CInt(year) + 1)
Else
monthName = WorksheetFunction.Text(DateSerial(0, Month(Date) + 1, 0), "MMMM")
End If
End If
Next i
End Sub

Function SheetExists(sheetName As String) As Boolean
Dim ws As Worksheet
SheetExists = False
For Each ws In ThisWorkbook.Sheets
If ws.Name = sheetName Then
SheetExists = True
Exit
Function
End If
Next ws
End Function

This code will create new tabs for each month based on a template tab named "Template". You can adjust the number of tabs created by changing the value of numberOfTabs. Make sure to replace "Template" with the name of your template sheet.

So, I just need to code all of this into a blank excel book?

 

[shina67]

Yes, you can add this VBA code into a blank Excel workbook to create a macro that will generate new tabs for each month based on a template tab named "Template."

 

[julhs]

Hear is something I was given years ago by “Alpha Frog” (and been using it ever since) with some minor additions.
It will create 12 new monthly sheets in your EXISTING workbook

' Create series of 12 new Monthly sheets
' code is assigned to a cmb button called "Create New Sheets"
'
Option Explicit
Option Compare Text 'makes typed text NON CASE sensitive

Sub Copy_Monthly_Template_Sheet()
  Dim i As Integer, j As Long, strName As String, yr As Long
      If MsgBox("You are about to create 12 New Sheets from the Template. Do you really want to continue?", _
vbQuestion + vbYesNo) = vbNo Then Exit Sub
      Application.ScreenUpdating = False
      Application.EnableEvents = False 'This prevents any Change Event procedure from firing.

     yr = 2021   'Don't need to change this "yyyy" as code will automatically find the last "yyyy" used.
Do Until Not Evaluate("ISREF('Dur Dec " & yr & "'!A1)")  'change "Dur" to suit the first part of your required sheet name.
     yr = yr + 1
   Loop
     j = Sheets("Dur Dec " & (yr - 1)).Index 'Again change "Dur" to suit the first part your required sheet name
  For i = 1 To 12
      strName = "Dur " & Format(i & "/" & yr, "mmm yyyy") 'Again change "Dur" to suit the first part your required sheet name
  If Evaluate("ISREF('" & strName & "'!A1)") Then 'Test if worksheet name already exists
      MsgBox strName, , "This Sheet already Exists, will skip to the next one"
   
    Sheets(strName).Move After:=Sheets(j)
Else
    Sheets("Dur Templ 2019").Copy After:=Sheets(j) 'This is name of Template to copy, change it to suit yours
   ActiveSheet.Name = strName
End If
     j = j + 1
  Next i
    Application.ScreenUpdating = True
    Application.EnableEvents = True 'Re-enable Change Event procedures
End Sub

What you should end up with is 12 new Monthly sheets, placed AFTER “DEC” of the LAST year named monthly sheet.
For instance, in my case the monthly sheets are named “Dur Jan 2022” through to “Dur Dec 2022”, when I run the code 12 new sheets will be created “AFTER” Dur Dec 2022 named “Dur Jan 2023 through to “Dur Dec 2023”