KD Jayakody

Call Us: 076 72 33 595

Macro to Generate Sheets Automatically!

Jan 18, 2024

Microsoft Excel is a powerful tool for data analysis, reporting, and organization. However, as your Excel workbooks become more complex, you may find it beneficial to automate certain tasks to save time and reduce errors. Two common tasks are creating monthly sheets and deleting unnecessary sheets. In this article, we will explore two VBA (Visual Basic for Applications) macros that can help you achieve these tasks efficiently and effectively.

Creating Monthly Sheets:
Creating monthly sheets is a common requirement in Excel when you want to maintain a structured organization of data for different months of the year. With VBA, you can automate this process. Here’s a VBA macro that creates sheets for each month from January to December while preserving your existing sheets:

Sub CreateMonthlySheets()
Dim i As Integer
Dim MonthName As String

' Loop through months from January (1) to December (12)
For i = 1 To 12
MonthName = Format(DateSerial(Year(Date), i, 1), "mmmm") ' Get the month name

' Check if a sheet with the same name already exists
If Not SheetExists(MonthName) Then
Sheets.Add(After:=Sheets(Sheets.Count)).Name = MonthName ' Create a new sheet with the month name
End If
Next i
End Sub

Function SheetExists(sheetName As String) As Boolean
On Error Resume Next
SheetExists = Not Sheets(sheetName) Is Nothing
On Error GoTo 0
End Function

This macro loops through the months and creates a new sheet with the month’s name if a sheet with that name doesn’t already exist. It uses the SheetExists function to check for existing sheets.

Deleting All Sheets Except the Current One:
Cleaning up your Excel workbook by deleting unnecessary sheets is crucial for maintaining a well-organized file. However, you may want to keep the current sheet intact while deleting the others. Here’s a VBA macro that does just that:


Sub DeleteAllSheetsExceptCurrent()
Dim ws As Worksheet
Dim CurrentSheet As Worksheet

' Store a reference to the current sheet
Set CurrentSheet = ThisWorkbook.ActiveSheet

' Loop through all sheets in the workbook
For Each ws In ThisWorkbook.Sheets
' Check if the sheet is not the current sheet
If ws.Name <> CurrentSheet.Name Then
' Delete the sheet
Application.DisplayAlerts = False ' Disable confirmation prompt
Application.DisplayAlerts = True ' Re-enable confirmation prompts
End If
Next ws
End Sub


This macro first identifies the current sheet using ThisWorkbook.ActiveSheet and then loops through all sheets in the workbook, deleting any sheet that doesn’t match the name of the current sheet. It temporarily disables confirmation prompts to streamline the deletion process.

With these VBA macros, you can efficiently manage your Excel workbooks by automating the creation of monthly sheets and deleting unnecessary sheets. These tools can help you save time and maintain a structured and organized workspace, especially when dealing with large and complex Excel files. Excel’s versatility combined with VBA macros can significantly improve your productivity and data management capabilities.


Recent Posts