Thursday, 15 April 2010

Object Required Error in excel VBA while trying to rename the sheet -


I am trying to copy a particular sheet and trying to designate it in the final form. The code is as follows:

  Sub CreateSheet () worksheets ("Main Matrix"). Copy (first: = worksheet (1)). Name = "Final" End Sub  

The program performs the copy of the sheet with the content, but then it throws the error and the name of my new sheet does not change in the final form.

Thanks in advance.

The problem is with regard to nesting which you are taking out Copy does not return a sheet object, so your error.

Try it out:

  Sub-startsheets with Excel () with Excel.thisworkbook Copy worksheet ("main matrix"): Before: = worksheet (1) .activesheet.Name = "last" end and end subscription with  

here is the QA that confirms this :

If this is going to be a small self-contained assistant routine, you can create a function with the worksheet return type:

< Pre> sub tst () Dim mySht as Excel.Worksheet set mySht = crtSheet ("foobar", "barfoo") & lt; & Lt; This last sub-function is used by the CRTSheet (by String as ByVal tgtSheet, as String in ByVal as NewName) as Excel.Worksheet Dim Excel With worksheet excel This workbook Worksheets (TGTSSet). Before Copy: Worksheets (1) S = Set. Activate s.Name = Set with new name closing crtSheet = s end function

No comments:

Post a Comment