Making of Expense Sheet
Sheet Renaming and checking for duplicate sheet
Dim j, str, temp
If Name “xps” Or Name “XP Monthly” Or Left(Name, 3) = “XP-” Then
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
Dim flag
flag = 0
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
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
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.
‘ 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.
