Create a Auto generating Excel expense sheet with VBA


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
 Else
  Exit For
 End If
Next
Range(“A7”) = str ‘ Name of the Month
‘………………………………………………………………………
End If
‘If (Left(Name, 3) = “xps” Or Name = “Monthly”) Then
Dim flag
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
 Exit For
End If
Next
‘………………………………………………………………………
If (flag > 0) Then MsgBox “Naming is possible”
Dim nameStr As String
nameStr = “XP-” & MonthName(Month(Date)) & ” ” & Year(Date)
Dim i
‘ 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”
  Exit For
 End If
Next
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
End If
End If
End Sub

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.

Sub NewXPS()
‘ NewXPS Macro
Sheets(“XP Monthly”).Select
Sheets(“XP Monthly”).Copy After:=Sheets(1)
ActiveSheet.Name = “XP-” & MonthName(Month(Date)) & ” ” & Year(Date) & ” ” & Rnd((1))
End Sub

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.

You can download and use complete Work Book from our Doc storage.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.