Tuesday, October 23, 2012

Excel macro to copy the contents from one worksheet to another

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



No comments:

Post a Comment