Few months back, i was calculating out my monthly expenses and thought of using excel for keeping the track of records. I felt that it was 'OK' and probably I can copy the same sheet for the coming months and then a rename and some tweaking will serve my purpose.
After one or two months, I got bored by this activity [ selecting the worksheet and then copying the contents to a new worksheet ] Then an idea crept to my mind "why don't i use the excel macro and do the same thing" instead of doing it manually.
So, here is the code for that :-
I have One worksheet with two sheets ( Template, Standard ). "Template" is the sheet which is having a set of well defined expenses and "Standard" is the sheet having the button using which i can create a new sheet.
"How to insert a button in excel ?" [ " Next Month " is the button name in my case ]
The macro which is to be added for this is :-
**************************************************
Private Sub Next_Month_Click()
Dim SheetVar As Worksheet
' Application.DisplayAlerts = False
For Each SheetVar In ActiveWorkbook.Worksheets
If SheetVar.Name = Format(Now, "mmmm_yyyy") Then
MsgBox (" The Sheet already exist")
End
End If
Next SheetVar
myWorksheetName = Format(Now, "mmmm_yyyy")
Sheets("Template").Copy After:=ActiveSheet
ActiveSheet.Name = myWorksheetName
Sheets(myWorksheetName).Activate
End Sub
**************************************************
So, this will solve the purpose of adding the sheet and if the sheet is already present then this will give a message that " The Sheet already exist"
Enjoy :)
Enjoy Testing and Keep Sharing
After one or two months, I got bored by this activity [ selecting the worksheet and then copying the contents to a new worksheet ] Then an idea crept to my mind "why don't i use the excel macro and do the same thing" instead of doing it manually.
So, here is the code for that :-
I have One worksheet with two sheets ( Template, Standard ). "Template" is the sheet which is having a set of well defined expenses and "Standard" is the sheet having the button using which i can create a new sheet.
"How to insert a button in excel ?" [ " Next Month " is the button name in my case ]
The macro which is to be added for this is :-
**************************************************
Private Sub Next_Month_Click()
Dim SheetVar As Worksheet
' Application.DisplayAlerts = False
For Each SheetVar In ActiveWorkbook.Worksheets
If SheetVar.Name = Format(Now, "mmmm_yyyy") Then
MsgBox (" The Sheet already exist")
End
End If
Next SheetVar
myWorksheetName = Format(Now, "mmmm_yyyy")
Sheets("Template").Copy After:=ActiveSheet
ActiveSheet.Name = myWorksheetName
Sheets(myWorksheetName).Activate
End Sub
**************************************************
So, this will solve the purpose of adding the sheet and if the sheet is already present then this will give a message that " The Sheet already exist"
Enjoy :)
Enjoy Testing and Keep Sharing
No comments:
Post a Comment