Monday, March 14, 2011

How to Create New Excel Sheet From Macro

You may face some problem during creation of new excel sheet which doesnot exist during VBA macro execution. Here is the simple solution for this.

You want to execute some macro which will do some operation on some sheet (say "TEST"). If sheet exists macro need to do operaion otherwise macro should create new sheet and do operaion.


Function FindSheet(ByVal SheetName As String)

On Error Resume Next
If Worksheets(SheetName) Is Nothing Then
Sheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = SheetName
End If
On Error GoTo 0

End Function

This function will search for given sheet name (say "TEST"). if exists it will come out of function otherwise it will create new sheet at the end of last sheet and rename that to sheetname "TEST" and returns.

So your main program will run without any interruption, irrespective of sheet name exists previously or not..

Kindly let me know you comments....