Microsoft Excel becomes one of my favourite application since I marry Excel macros, it help me lot to minimize tedious jobs. Here I will explain how you can perform an advanced Macro operation with the help of VB coding operations in Microsoft Excel 2013/2010/2007.
Making of Expense Sheet
Firstly design your expense sheet as you like and Name the worksheet says ‘XP monthly’ which is short for Expense Monthly. It will be the base sheet which is working as source for additional sheet.
Sheet Renaming and checking for duplicate sheet
This task will require VB coding, just hit Alt+F11 to activate VB Editor and drop the following codes in Sheet1(XP Monthly).
Private Sub Worksheet_Activate()
Dim j, str, temp
If Name “xps” Or Name “XP Monthly” Or Left(Name, 3) = “XP-” Then
‘ FIND THE SPACE IN IN SHEET NAME FOR TITLE
For j = 4 To Len(Name)
temp = Mid(Name, j, 1)
If temp ” ” Then
str = str & temp
Range(“A7”) = str ‘ Name of the Month
‘If (Left(Name, 3) = “xps” Or Name = “Monthly”) Then
flag = 0
‘ FIND THE PERIODS IN SHEET NAME
For j = Len(Name) To 1 Step -1
temp = Mid(Name, j, 1)
If temp = “.” Then
flag = 1
If (flag > 0) Then MsgBox “Naming is possible”
Dim nameStr As String
nameStr = “XP-” & MonthName(Month(Date)) & ” ” & Year(Date)
‘ CHECK WHETHER THE SHEET NAME ALREADY TAKEN
Dim h, yy
For h = 1 To Worksheets.Count
If Sheets.Item(h).Name = nameStr Then
foo = True
MsgBox “Sorry the XPs Sheet already exist for this month,Rename the sheet manually”, vbCritical, “Naming Error”
If foo = False Then
i = MsgBox(“Do you want to alter the worksheet name as ” & “‘” & nameStr & “‘” & “?”, vbYesNo, “Naming offer”)
If i = vbYes Then Name = nameStr
For avoiding the accidental renaming we add some numbers (NewXPS)with sheet name when it is duplicating.Above action will be perform when user switch between sheets during which Activate event of Sheet is fired.
Copying the Base sheet
Now we are ready create new Macro for copying the Base sheet for current month.Press Alt+F8 and enter macro name XPSheet and hit Create button and drop the following Macro code.
‘ NewXPS Macro
Sheets(“XP Monthly”).Copy After:=Sheets(1)
ActiveSheet.Name = “XP-” & MonthName(Month(Date)) & ” ” & Year(Date) & ” ” & Rnd((1))
The Rand() will generate random number file name, which used to avoid duplicating sheets. The sheet will be renamed later as current month, if there is no duplicate sheet otherwise you have rename it manually.
How to use this Macro enabled sheet
Whenever you need a new Expense sheet just run the Macro and switch between sheets once, it will rename automatically. VB Macro programming offer flexibility and ease of peace, working with MS Excel.